September 17, 2010 at 8:02 am
Hi there,
I have a user that has SELECT access to a table in a database. The table does not have ANY explicit permissions assigned to it. I have checked db_datareader and db_owner and sysadmin roles to see if the user is a member of those either explicitly or through an AD Group. I can't seem to find him. I have run the following query to confirm his permissions:
EXECUTE AS USER = 'DOMAIN\MyUser';
select * FROM fn_my_permissions('[dbo].[MyTable]', 'Object')
ORDER BY subentity_name, permission_name ;
REVERT;
Is there a way I can supply a user name and find out all the permissoins that user has? Alternatively supply a table name and discover all the permissions and the users that have them? I'm pulling my hair out.
Thanks.
September 17, 2010 at 8:22 am
This should cover most of the rights (to be used inside EXECUTE AS xxx) SELECT b.name, a.name, c.*
FROM sys.objects a
Join sys.schemas b on b.schema_id = a.schema_id
CROSS APPLY fn_my_permissions (b.name + '.' + a.name, 'OBJECT') c
September 20, 2010 at 1:41 am
Thanks, that's not quite what I'm after. I know that the user has SELECT permissions but I don't know HOW they have them. There are no explicit permissions granted to the table and the user is not in the db_datareader, db_owner or sysadmin roles. Any ideas?
September 20, 2010 at 4:48 am
You can check which roles have db_datareader assigned to them (or Select) and see if the user is in one of these role.
You can use the tables sys.database_permissions and sys.database_principals to find out what is granted to which group:Select
prmssn.state_desc,-- Grant, Deny, etc...
prmssn.permission_name,-- CONNECT, SELECT, DELETE, INSERT, UPDATE
ISNULL(' on ' + syso.name,'') as 'Object',
ISNULL(' to ' + grantee_principal.name,'') as 'User'
From sys.database_permissions prmssn
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
LEFT JOIN sys.objects AS syso ON prmssn.major_id = syso.object_id
Where grantee_principal.name != 'public'-- Get rid of public
Order by grantee_principal.name
September 20, 2010 at 6:29 am
Thanks Oliii, that doesn't give me what I need either. I've already checked db_datareader. If I run this I get zero rows:
Select
prmssn.state_desc, -- Grant, Deny, etc...
prmssn.permission_name, -- CONNECT, SELECT, DELETE, INSERT, UPDATE
ISNULL(' on ' + syso.name,'') as 'Object',
ISNULL(' to ' + grantee_principal.name,'') as 'User'
From sys.database_permissions prmssn
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
LEFT JOIN sys.objects AS syso ON prmssn.major_id = syso.object_id
Where grantee_principal.name != 'public' -- Get rid of public
and syso.name = 'mytable'
Order by grantee_principal.name
However the user has SELECT permissions on that table (mytable) - but I don't know how. Any further ideas?
September 20, 2010 at 7:11 am
That means he got a grant select on all tables (or that public has been granted select) or he has a select trough a view.
You'll have to look for it the long way:Select *
From sys.database_permissions prmssn
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
In short what you are looking for is a state_desc = GRANT, permission_name = SELECT (or even EXECUTE) and a major_id >= 0.
The rows with major_id = 0 are Select granted on all tables and views, check he's not in one of the group or roles (column 'name'), major_id > 0 can be joined to sys.objects to get the table/view name.
He can either have select directly on the table, or he can select trough a view or even a stored proc.
Hopefuly this will show you how he can do a SELECT on the table, but it'll not show you who gave it though.
September 20, 2010 at 11:28 am
First question: Is this a Windows account or a SQL account? That makes a difference.
If Windows, you need to find out from the AD people what Windows Groups this user is a member of. Other options include (if you're in the same domain as this user) going to Start -> Run and type in "cmd" (without the quotes). Then type in "net user <username> /domain" to get a list of what groups this user is a member of.
Then check ALL the Windows Groups in both Server and database level Security. See what permissions these groups have. Deny overrides Grant, but in this case, I'm assuming there are no denies.
If it's a SQL login, post back and I'll see if I can whip up some code to track down what you're looking for.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply