List All Permissions a User Has in SQL Server Database and Error 4064
October 9th, 2008 by Andrew Chen
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:
(2 votes, average: 3 out of 5)
Loading ...
fn_my_permisssions does not work for me in SQL 2000. Is there any other function/query that would work in the SQL 2000 and give the same information as fn_my_permissions.
I want to check/get access rights of a user for a specific table in a database.
Thanks!
fn_my_permissions does not work in SQl 2000. Is there any similar function/command for SQL 2000.
I need to check the user access rights of specific tables in a database.
Thanks
For SQL 2000 you can use permissions(). Although the result is a bitmap and much harder to use.
See msdn.microsoft.com/en-US/library/ms186915%28v=SQL.90%29.aspx
i tried to execute the above query but doesn’t work. I m using sa account and following error appears “Cannot execute as the database principal because the principal “u_reenam_prd” does not exist, this type of principal cannot be impersonated, or you do not have permission.” Please help me on this error and i executed the following query ” execute (’select * From fn_my_permissions(NULL, “DB_name”)’)
AS USER = ‘login_name’ “.
Thanks in Advance
This is very useful and all works fine.