Siusic Dot Com

SQL Server permissions can be granted on server, database, schema, and database object level. In SQL server 2005 the permissions can be controlled at a more granular level compare to SQL Server 2000. For example in SQL 2000 if you want to grant someone the permission to run profiler you have to put it into the “Server Administrators” server role. And that role implies other permissions. In SQL 2005 you may just grant trace to specific user using

Grant Alter Trace to Someone

To list all the permissions that can be controlled you can use the function fn_my_permission. Remember to login using an account that has sysadmin role. The function actually list all the permission you have.

This query lists all permissions on server:
select * From fn_my_permissions(NULL, NULL)

This query lists all permissions on database:
select * From fn_my_permissions(NULL, ‘database’)

This query lists all permission on the dbo schema:
select * From fn_my_permissions(’dbo’, ’schema’)

This Query lists all permission on a table
select * From fn_my_permissions(’dbo.test’, ‘object’)

What if you want to know what permissions someone has in your database? Since you are sysadmin you can impersonate anyone. The following query will impersonate another user to run the fn_my_permissions function

execute (’select * From fn_my_permissions(NULL, ”database”)’) AS USER = ‘Someone’

If a user has access to a database. The above query at least should return the following

entity_name permission_name
------------ ----------------
database CONNECT

If you don’t see that then a login can not access a particular database even when it is configured as a database user. If it happen that the database is configured as the default database for that login then it will appear that the login fails to connect to the server. It will report this message “cannot connect to [server name]. Cannot open user default database. Login failed. Login failed for user [user name]. (Microsoft SQL Server Error: 4064)

Related Posts:

How to Restore a Database Used By Web Site 24/7?

The Best Way to Backup SQL Server

The Best Way to Grant Execution Right on xp_cmdshell and Cross Database Ownership Chaining

T-SQL Query to Get Database Size

Master.mdf is Compressed But Does not Reside in a Read-only Database or Filegroup. The File Must be Decompressed.