Script to gett all permission in database

Question

Post reply

Script to gett all permission in database

Nido786

Default port

Points: 1482
More actions
April 5, 2009 at 5:22 am

#201787

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

Try this: http://www.sqlservercentral.com/scripts/31776/

Nido786 Default port Points: 1482 More actions · Answer 2

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.

sharon-644450 Default port Points: 1442 More actions · Answer 3

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.

Nido786 Default port Points: 1482 More actions · Answer 4

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......

prem Mr or Mrs. 500 Points: 508 More actions · Answer 5

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