Checking permissions related to Logins created

To find out what is exact level of permissions that a particular user is having on Server level, database level and object level, we have to use the table valued functions
fn_my_permissions ( securable , 'securable_class' )
and

HAS_PERMS_BY_NAME ( securable , securable_class , permission
[ , sub-securable ] [ , sub-securable_class ] )

To find permissions on server:

SELECT * FROM sys.fn_my_permissions(default, ‘SERVER’);

To find permissions on database:

SELECT * FROM sys.fn_my_permissions(default, ‘DATABASE’);

To find permissions on objects:

SELECT * FROM sys.fn_my_permissions(‘usp_collect_stats’, ‘OBJECT’);

===========================================================================
We can simply query to master database to get who all the users are having permissions on server level.

Server level permissions are the following:

public
sysadmin
securityadmin
serveradmin
setupadmin
processadmin
diskadmin
dbcreator
bulkadmin
Query:



select
A.name 'LOGIN NAME',A.type_desc 'Type of Login',A.default_database_name 'Default DB',
'Permission type' = Case B.role_principal_id
When 2 Then 'public'
When 3 Then 'sysadmin'
When 4 Then 'securityadmin'
When 5 Then 'serveradmin'
When 6 Then 'setupadmin'
When 7 Then 'processadmin'
When 8 Then 'diskadmin'
When 9 Then 'dbcreator'
When 10 Then 'bulkadmin'
End
from sys.server_principals A join sys.server_role_members B on A.Principal_id=B.member_principal_id
where
Sid in (Select Sid from sys.syslogins)
and type in ('U','G','S')
===========================================================================Similarly we find the permissions at database level also, we can query each database to find out the users having specific previlegs on the database.

Below query needs to run a particular database to find its users and their permissions:


Use
Go
select A.name 'USER NAME',A.type_desc 'TYPE OF USER',A.default_schema_name 'DEFAULT SCHEMA',
'Permission type' = Case B.role_principal_id
when 0 Then 'public'
when 16384 Then 'db_owner'
when 16385 Then 'db_accessadmin'
when 16386 Then 'db_securityadmin'
when 16387 Then 'db_ddladmin'
when 16389 Then 'db_backupoperator'
when 16390 Then 'db_datareader'
when 16391 Then 'db_datawriter'
when 16392 Then 'db_denydatareader'
when 16393 Then 'db_denydatawriter'
End
from
sys.database_principals A
Join
sys.database_role_members B on A.principal_id=B.member_principal_id
=======================

In SQL Server 2005, there is a stored procedure which helps to run the query in all the existing databases of the server and will return the results accordingly. That stored procedure is sp_MSForEachDB..

Using the above procedure in database level query and run it in master database...


Use master
Go
EXEC sp_MSForEachDB 'Use ?;
select A.name ''USER NAME'',A.type_desc ''TYPE OF USER'',A.default_schema_name ''DEFAULT SCHEMA'',
''Permission type'' = Case B.role_principal_id
when 0 Then ''public''
when 16384 Then ''db_owner''
when 16385 Then ''db_accessadmin''
when 16386 Then ''db_securityadmin''
when 16387 Then ''db_ddladmin''
when 16389 Then ''db_backupoperator''
when 16390 Then ''db_datareader''
when 16391 Then ''db_datawriter''
when 16392 Then ''db_denydatareader''
when 16393 Then ''db_denydatawriter''
End
from
sys.database_principals A
Join
sys.database_role_members B on A.principal_id=B.member_principal_id';

=======================

Hope the above information is useful..