Find out what permission

Question

Hi Team,

Today morning one of the user i have given some permissions datareader etc..

How can we find out xxx user what permissions & What Roles  he owns

How can we check please suggest me.

I have tried in this way.

SELECT
 SDP.PRINCIPAL_ID AS [Principal ID],
 SDP.NAME AS UserName,
 SDP.TYPE_DESC AS UserType, 
 SSP.NAME AS LoginName,
 SSP.TYPE_DESC AS LoginType
FROM sys.database_principals SDP
INNER JOIN sys.server_principals SSP
ON SDP.PRINCIPAL_ID = SSP.PRINCIPAL_ID
GO

o/p:

6 xxxx SQL_USER setupadmin SERVER_ROLE

Today i have give access abc database as data_reader, i have seen in user--> property--> usermappings --database name here it is not showing any thing.

I have checked that user account is connecting to the server after that

click the database it is giving errror

The database AdminMig is not accessible. (ObjectExplorer)

Tx

---

subu

Answer 1

Hi,

You may take a look on this article for database level permissions.

I hope it helps.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 2

Hi subu,

It seems that the user you used to access abc database has not been grant data_reader permission successfully, it even has no public permission, please have a check.

For quering permissions assigned to users and roles, you can also refer to this thread: http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/1e20a9e4-a6d1-40a0-ab07-bd48cda4754f

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.