Can you query SQL Server permissions?

[Axoliien]

I am trying to find a way to determine if a user is allowed access to tables in SQL server so I can handle permission management on the client side (a nice GUI to tell them not to do stuff they shouldn't be doing). I figure there should be some way to determine if the currently logged in user is allowed permissions to tables using the standard windows login information given to SQL server. Has anyone done anything like this, or do you have some brainstorming ideas that may help?

My only clue right now is to create a stored procedure that allows anyone to run it, then retrieve the currently logged in user name. I don't really know what to do from there, but any help or other idea is appreciated!

 

[dhulbert]

Axoliien

Have a look at the SQl Server help files under
"ListUserPermissions Method" adn it should solve your questions

The outline Syntax is
object.ListUserPermissions( UserName ) as SQLObjectList

 

[mrdenny]

Look in BOL under INFORMATION_SCHEMA.TABLE_PRIVILEGES.

select *
from INFORMATION_SCHEMA.TABLE_PRIVILEGES
where GRANTEE = user_name()

That should return all the objects the person has rights to.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[mrdenny]

Look in BOL under INFORMATION_SCHEMA.TABLE_PRIVILEGES.

select *
from INFORMATION_SCHEMA.TABLE_PRIVILEGES
where GRANTEE = user_name()

That should return all the tables the person has rights to.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)