Tags Tags

How to find out database role or server role in SQL 2005

July 22nd, 2010 - 04:54 am ET by MS Techie | Report spam
Help Create a new topic
Hi All,

Currently I have a scenario wherein a user has logged in via the Windows
Authentication to the SQL Server 2005 using SQL Server Management Studio. Now
the user does to what all database roles(like db_datareader,db_datawriter
etc..) he has access to and what all Server Roles he as access to.

(Since there are 2 kinds of roles (1)Database Roles (2) Server Roles )

Now When the user expands the logins folder in the SQL Server Management
Studio , he sees several Windows NT Active Directory Security Groups listed
over there , but does not see his windows NT login Id listed over there. This
means to see that he must be belonging to one of the Active Directory's
Security Group. Since Domain\Domain Users is one of the groups listed over
there, he would belong to that group and may be one more Security Group. Now
is there is any command or way to find out what Active Directory Security
Group he belongs to or what database roles and server roles he posses on that
SQL Server Instance.

Thanks in Advance
email Follow the discussionReplies 1 replyReplies Make a reply

Replies

#1 Erland Sommarskog
July 22nd, 2010 - 05:46 am ET | Report spam
MS Techie () writes:
Currently I have a scenario wherein a user has logged in via the Windows
Authentication to the SQL Server 2005 using SQL Server Management
Studio. Now the user does to what all database roles(like
db_datareader,db_datawriter etc..) he has access to and what all Server
Roles he as access to.

(Since there are 2 kinds of roles (1)Database Roles (2) Server Roles )

Now When the user expands the logins folder in the SQL Server Management
Studio , he sees several Windows NT Active Directory Security Groups
listed over there , but does not see his windows NT login Id listed over
there. This means to see that he must be belonging to one of the Active
Directory's Security Group. Since Domain\Domain Users is one of the
groups listed over there, he would belong to that group and may be one
more Security Group. Now is there is any command or way to find out
what Active Directory Security Group he belongs to or what database
roles and server roles he posses on that SQL Server Instance. >



To see what Active Directory groups he is, I think the AD is the place to
look in.

To see what a permissions a user has, you can use the function run
fn_my_permissions or has_perms_by_name. Note that they must be executed
in the context of that user.


Erland Sommarskog, SQL Server MVP,

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sql...14207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sql...95970.aspx
SQL 2000: http://www.microsoft.com/sql/prodin...books.mspx

Similar topics

Make your own search :