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

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

MS Techie (MSTechie@discussions.microsoft.com) 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, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx