SQL Server ~ Viewing User Permissions

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:

SELECT
name, type_desc, default_schema_name
FROM
sys.database_principals

sys.database_principals
sys.database_principals example

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:

SELECT
class_desc,
case when class=1 then object_name(major_id)
when class=3 then schema_name(major_id)end,
USER_NAME(grantee_principal_id),
permission_name,
state_desc
FROM
sys.database_permissions

This query produces the following results:

sys_database_permissions
sys_database_permissions

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.

Advertisements

One thought on “SQL Server ~ Viewing User Permissions

  1. Two of the coolest tools that have ever come out from Microsoft are certainly Microsoft SQL Server and Microsoft Access. Microsoft Access databases have certainly become more prevalent over the last 2 years as large corporations break the imposed ban on using the tool. Many of our large corporate clients and government clients have stymied development in Microsoft Access for varying reasons which can include security issues or simply the IT Managements belief that they don’t want to support the application.

Have an opinion? Leave an opinion!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s