As an SQL Server admin, there are times when the combination of many users, groups, roles, logins and the various permissions that can be given to each to do stuff on various databases and objects within your server may just be too confusing. Just like the previous sentence.
One way of coming out of this mess unscarthed is to rigorously document every permission granted to every login/group and keep track of it all. I know many people who don't. I don't either. Since you basically can't do much with SQL Server unless 'explicity' granted or 'implicitly' allowed by virtue of your membership in the public or other groups/roles, I am normally much more worried about what I have GRANTED my logins rather than what I have explicitly DENIED them. In other words, most people would love to know who CAN do stuff rather than who CANNOT do stuff. So what else can you do about it? I wrote a script (stored procedure actually) to show me all the stuff that a given user/login can do within a particular database and on its hosting server. Here we go.
CREATE PROC [dbo].[loginperms]
@login
varchar(128)
AS
SET
NOCOUNT ON
IF (SELECT is_disabled from sys.server_principals WHERE name=@login)=1
SELECT 'The login <' + @login + '> is disabled'
ELSE
SELECT [Object Class]=class_desc, [Object]=CASE class WHEN 100 THEN @@SERVERNAME
WHEN 105 THEN (SELECT name FROM sys.endpoints WHERE endpoint_id=major_id)
ELSE object_name(major_id) END ,[column]='',[Permission]=permission_name,
[Permission Type]
=CASE spr.type WHEN 'U' THEN 'Direct' WHEN 'R' THEN 'Inherited' END
FROM sys.server_permissions spm JOIN sys.server_principals spr
ON spm.grantee_principal_id=spr.principal_id
WHERE spr.type NOT IN('C') AND is_disabled=0 AND spr.name=@login
UNION
SELECT [Object Class]=class_desc,Object=CASE class WHEN 100 THEN @@SERVERNAME
WHEN 105 THEN (SELECT name FROM sys.endpoints WHERE endpoint_id=major_id)
ELSE object_name(major_id) END,'' ,permission_name,
[Permission Type]
=CASE spr.type WHEN 'U' THEN 'Direct' WHEN 'R' THEN 'Inherited' END
FROM sys.server_permissions spm JOIN sys.server_principals spr
ON spm.grantee_principal_id=spr.principal_id
WHERE spr.type NOT IN('C') AND is_disabled=0 AND spr.name='public'
UNION
SELECT [Object Class]=CASE class WHEN 1 THEN (SELECT REPLACE(type_desc,'_',' ') FROM sys.all_objects WHERE object_id=major_id) ELSE class_desc END ,
Object
=CASE class WHEN 0 THEN DB_NAME() ELSE object_name(major_id) END ,
[Column]
=CASE minor_id WHEN 0 then '' ELSE COL_NAME(major_id,minor_id) END,permission_name,
[Permission Type]
=CASE dpri.type WHEN 'U' THEN 'Direct' WHEN 'R' THEN 'Inherited' END
FROM sys.database_permissions dperm JOIN sys.database_principals dpri
ON dperm.grantee_principal_id=dpri.principal_id
WHERE dpri.name=@login AND state_desc LIKE 'G%' AND major_id>=0
UNION
SELECT [Object Class]=class_desc, Object=CASE class WHEN 0 THEN DB_NAME() ELSE object_name(major_id) END,
[Column]
=CASE minor_id WHEN 0 then '' ELSE COL_NAME(major_id,minor_id) END,
permission_name
,[Permission Type]=CASE dpri.type WHEN 'U' THEN 'Direct' WHEN 'R' THEN 'Inherited' END
FROM sys.database_permissions dperm JOIN sys.database_principals dpri
ON dperm.grantee_principal_id=dpri.principal_id
WHERE dpri.name='public' AND state_desc LIKE 'G%' AND major_id>=0
GO
You may want to use the procedure like this:
EXECUTE
[dbo].[loginperms] 'DOMAIN\user'
or
EXECUTE
[dbo].[loginperms] 'sql_login'
I know, it is even easier get this information using the brand new SQL Server Management Objects (SMO). Maybe I will blog about that on another day. In the meantime, let's just be happy with T-SQL.