Solved

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

Posted on 2011-09-08
7
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 40

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 40

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 40

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
sql server connection string in config file 4 41
Remove () 10 41
Need help with part of sql query for a condition 12 34
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

739 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