April 5, 2009 at 5:22 am
Does anyone has a SQL script that can start at the beginning of the logins (in the Security section for that server) and list, for each login, their respective server roles and database permissions? What about checking or scanning through a whole group of servers?
I have about 1500 SQL Servers to check through.
April 5, 2009 at 8:54 pm
April 6, 2009 at 2:54 am
Dear thank you so much for prompt reply but still i am confuse
simply i want to make a list of all DB/Windows users having with all permission.
for example ,
test user have read_only permission
monitor user have DB_owner permission
kkj\kirate user have right_only permission
i need to check all user with there respective rights.
April 6, 2009 at 4:48 am
you can try exec sp_helplogins. but you will have to run this for all the databases. may be you can write a small script and use this sp to get you the desired result.
hope this helps.
April 6, 2009 at 5:04 am
thanks for this
i am using sp_helplogins , but i need to execute on every server and database .
i made this query but not able to get permission.
select a.dbid,a.name as Username,b.dbid,b.name As Dataabase from
dbo.sysxlogins a, sysdatabases b
where a.dbid=b.dbid
order by 1 desc
i am still thinking that how to add permission in above query to get the actual result.
may be you have better idea than me......
April 13, 2009 at 4:44 pm
Hi
Please try this for scripting out the object permissions in a database.
SELECT
CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +
perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(schema_NAME(obj.schema_id)) + '.'
+ QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROMsys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
ORDER BY usr.name
Thanks
Prem
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply