Solved

Sql Server- how to check what permissions a specific user has

Posted on 2011-09-08
7
273 Views
Last Modified: 2012-05-12
How can I tell what db permissions my windows username has on our SQL server databases?  I have access to Sql Server 2008 R2 Management Studio, I just don't know where to look.  All of our db's use windows authentication. Thanks.
0
Comment
Question by:snyperj
  • 3
  • 3
7 Comments
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36506036
Hi,

so the sql server security model is two fold, firstly there is server security which are logins, found in the security -> logins folder of the server.

each database then has users (same place but at DB level, Security -> Users) and a login is mapped to a user per database and it is the user that defines the permissions at the database level.


Hope this answers your questions


Thanks

Dave
0
 
LVL 39

Expert Comment

by:lcohan
ID: 36506089
The code below should give you all user rights in a database but if you want only one login than you can just run: SELECT * FROM fn_my_permissions(NULL, 'DATABASE')


select
 sysusers.name as username, sysusers.gid,
 sysobjects.name as objectname, sysobjects.id,
 CASE WHEN sysprotects_1.action is null THEN CASE WHEN sys.sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'SELECT',
 CASE WHEN sysprotects_2.action is null THEN CASE WHEN sys.sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'INSERT',
 CASE WHEN sysprotects_3.action is null THEN CASE WHEN sys.sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'UPDATE',
 CASE WHEN sysprotects_4.action is null THEN CASE WHEN sys.sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'DELETE',
 CASE WHEN sysprotects_5.action is null THEN CASE WHEN sys.sysobjects.xtype = 'U' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'EXECUTE'
from
 sys.sysusers
 full join sys.sysobjects on ( sysobjects.xtype in ( 'P', 'U' ) and sysobjects.Name NOT LIKE 'dt%' )
 left join sys.sysprotects as sysprotects_1
  on sysprotects_1.uid = sysusers.uid and sysprotects_1.id = sysobjects.id and sysprotects_1.action = 193 and sysprotects_1.protecttype in ( 204, 205 )
 left join sys.sysprotects as sysprotects_2
  on sysprotects_2.uid = sysusers.uid and sysprotects_2.id = sysobjects.id and sysprotects_2.action = 195 and sysprotects_2.protecttype in ( 204, 205 )
 left join sys.sysprotects as sysprotects_3
  on sysprotects_3.uid = sysusers.uid and sysprotects_3.id = sysobjects.id and sysprotects_3.action = 197 and sysprotects_3.protecttype in ( 204, 205 )
 left join sys.sysprotects as sysprotects_4
  on sysprotects_4.uid = sysusers.uid and sysprotects_4.id = sysobjects.id and sysprotects_4.action = 196 and sysprotects_4.protecttype in ( 204, 205 )
 left join sys.sysprotects as sysprotects_5
  on sysprotects_5.uid = sysusers.uid and sysprotects_5.id = sysobjects.id and sysprotects_5.action = 224 and sysprotects_5.protecttype in ( 204, 205 )
where -- sys.sysusers.name = 'beny'            -- by USER
            sysobjects.name = 'clients'      -- by OBJECT
order by
 sysusers.name, sysobjects.name

0
 

Author Comment

by:snyperj
ID: 36506244
Well, I was in the right place, but, it looks like they way we our configured- everything is by groups.  There are no individual user names in the Users folder, just group names.    However when I click on one of them... I don't see where it tells me what usernames are in the group.

In line with that, lcohan, the query comes back empty..
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 36506349
groups? Like NT groups I ssume right? and NT logins part of the local group?
If you're talking about roles this make more sense for a SQL (and not only) database security model in which case you can run sp_helprolemember in the DB.
0
 

Author Comment

by:snyperj
ID: 36506402
hee hee... ok, I am really a novice.  I found it 'sp_helpfrolemember'   ... how do I run it?
0
 

Author Comment

by:snyperj
ID: 36506429
I figured it out... thanks for the help
0
 
LVL 39

Expert Comment

by:lcohan
ID: 36511687
No problem - anytime.
That's why we meet here to share our knowledge and help if we can.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now