Today I am going to talk briefly about user permissions. User accounts are different than logins. Logins are server level accounts that grant access to a SQL Server. User accounts handle permissions to databases and the objects contained in them.
There are a couple places to look for permissions of user accounts: sys.database_principals and sys.database_permissions. In SQL Server, a principal is an object to whom permissions can be granted, for example database users, logins, and roles. So the sys.database_principals contains a list of users and roles. Issuing the following statement returns the results below:
name, type_desc, default_schema_name
This is useful but not what we are looking for. This is a list of all principals within my adventureworks database. Other fields included in this table are the SID and create/modify dates. Default schemas allow a user to not include the schema for an object in T-SQL. For example, assume table1 was in the schema dbo. Dbo could execute select * from table1, but guest would have to execute select * from dbo.table1. More on schemas in another post.
Sys.database_permissions is a dmv that has all of the database level permissions, no shock there it’s in the name. The table is actually kind of cryptic with out the help of Books Online. It also helps to use the user_name and object_name functions. The below query below will certainly help make it more readable:
case when class=1 then object_name(major_id)
when class=3 then schema_name(major_id)end,
This query produces the following results:
Take a look at the bottom row, this is saying that the user, testuser, has select permissions on the schema dbo.
There are three settings for permissions. They are GRANT, REVOKE, and DENY. Grant gives permission to a user. Revoke removes permission to a user. Deny will prevent the specified action on an object, even if it’s inherited from another permission.
Note: There is a thing called a guest user, you almost never want it enabled. Always read through Books Online if you are thinking of turning it on. Generally, just create an account with the least priviledges needed to perform the task necessary.