Sql Server- how to check what permissions a specific user has
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.
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
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
ASKER
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..
In line with that, lcohan, the query comes back empty..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hee hee... ok, I am really a novice. I found it 'sp_helpfrolemember' ... how do I run it?
ASKER
I figured it out... thanks for the help
No problem - anytime.
That's why we meet here to share our knowledge and help if we can.
That's why we meet here to share our knowledge and help if we can.
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