SQL Server logins

Question

Post reply

SQL Server logins

sqlservering

SSC Veteran

Points: 222
More actions
July 21, 2008 at 4:00 am

#125017

Hi, how to find the permissions of a login in a database sql server 2005. any query to find that??

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Check for the system table sys.fn_my_permissions OR sys.fn_builtin_permissions

SELECT * FROM sys.fn_my_permissions( NULL,'database' )

--Ramesh

sqlservering SSC Veteran Points: 222 More actions · Answer 2

Ramesh (7/21/2008)
Check for the system table sys.fn_my_permissions OR sys.fn_builtin_permissions
SELECT * FROM sys.fn_my_permissions( NULL,'database' )

thanks for the quick reply..however when I excuted the above script, I didnt get any records. moreover I would like to know if we have any script that could be executed against a login. and that script will give the properties of that login.like select permissions etc...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

For server-level permissions, you can query sys.server_permissions and sys.server_principals thusly

SELECT NAME, permission_name, state_desc

FROM sys.server_permissions sp

INNER JOIN sys.server_principals pri ON sp.grantee_principal_id = pri.principal_id

For database-level permissions, you can do much the same with sys.database_permissions and sys.database_principals. Onlyy valid for the current DB

SELECT NAME, permission_name, class_desc , OBJECT_NAME (major_id) , state_desc

FROM sys.database_permissions dp

INNER JOIN sys.database_principals pri ON dp.grantee_principal_id = pri.principal_id

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlservering SSC Veteran Points: 222 More actions · Answer 4

GilaMonster (7/21/2008)
For server-level permissions, you can query sys.server_permissions and sys.server_principals thusly
SELECT NAME, permission_name, state_desc
FROM sys.server_permissions sp
INNER JOIN sys.server_principals pri ON sp.grantee_principal_id = pri.principal_id
For database-level permissions, you can do much the same with sys.database_permissions and sys.database_principals. Onlyy valid for the current DB
SELECT NAME, permission_name, class_desc , OBJECT_NAME (major_id) , state_desc
FROM sys.database_permissions dp
INNER JOIN sys.database_principals pri ON dp.grantee_principal_id = pri.principal_id

thanks, but think this is the same result as sp_helprotect..however I want a script that could be ran against a particular login. the result will given the properties of only that login!

vanessab Grasshopper Points: 20 More actions · Answer 5

vanessab

Grasshopper

Points: 20

July 22, 2008 at 2:27 am

#846217

Try :

sp_helplogins 'LoginName'

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 6

vanessab (7/22/2008)
Try :
sp_helplogins 'LoginName'

thats true..you could also use :

sp_helpuser youruserid

🙂

sqlservering SSC Veteran Points: 222 More actions · Answer 7

rinu philip (7/22/2008)
vanessab (7/22/2008)
Try :
sp_helplogins 'LoginName'
thats true..you could also use :
sp_helpuser youruserid
🙂

Thanks aton rinu..it worked ...koooolll

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 8

RGP

SSCarpal Tunnel

Points: 4381

July 22, 2008 at 3:29 am

#846248

Most Welcome:D

ezekielen SSC Enthusiast Points: 103 More actions · Answer 9

You can also query the database system views if a user has restrictive permissions

INFORMATION_SCHEMA.COLUMN_PRIVILEGES

INFORMATION_SCHEMA.table_privileges

Ezekiel