permissions for a login
-
how do i view all permissions set for a specific login?
thanks!
-
AFAIK, you will need some script to do this. There might be one on this site. In EM you can see the user permissions, but need to check the role permissions separately.
What type of output are you looking for? By Object?
Steve Jones
-
yes, by object
thx again
-
This will handle for one database.
SELECT
sysusers.[name] AS UserName,
sysobjects.[name] AS Object,
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 1) = 1 THEN 'Granted'
WHEN (actmod & 1) = 1 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [SELECT],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 8) = 8 THEN 'Granted'
WHEN (actmod & 8) = 8 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [INSERT],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 2) = 2 THEN 'Granted'
WHEN (actmod & 2) = 2 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [UPDATE],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 16) = 16 THEN 'Granted'
WHEN (actmod & 16) = 16 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [DELETE],
CASE WHEN xtype IN ('P','FN') THEN
CASE
WHEN (actadd & 32) = 32 THEN 'Granted'
WHEN (actmod & 32) = 32 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [EXEC],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 4) = 4 THEN 'Granted'
WHEN (actmod & 4) = 4 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [DRI]
FROM
syspermissions
INNER JOIN
sysusers
ON
uid = grantee
INNER JOIN
sysobjects
ON
sysobjects.[id] = syspermissions.[id]
---Optional
WHERE
sysusers.[name] = ''
To handle for all databases make the above a stored procedure in master and then use the following method.
EXEC sp_MSForEachDB 'PRINT ''?''
EXEC
..sp_MyPermissions'This should return the database name then the list, you may have to clean a bit to make work like you want.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
looks good to me. Thanks so much!
-
Defintely Check the SCRIPTS page. There are a lot of good examples in there. -jG
-JG
-JG
..sp_MyPermissions'Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply