none
Returning database roles for the Current User RRS feed

  • Question

  • Hi All,

     

    Im after a way to return the Database Roles the Current user is a member of so I can control program flow.  Anyone know the best way to do this?

     

    Thanks in Advance

     

    Alex

    Thursday, February 14, 2008 3:25 PM

All replies

  • Hello,

     

    You have several ways to get the databases roles the current user is a member

     

    1) you have the efficient solution to program with SMO . You will get you want , but to program, you must have to well know SMO and it's not evident ( on the beginning but afterward you will see that's very simple )

     

    2) you may use a system stored procedure see this link:

    http://msdn2.microsoft.com/en-us/library/ms178021.aspx

     

    2) always with a system stored procedure see:

    http://msdn2.microsoft.com/en-us/library/ms189780.aspx  ( not the easiest way )

     

    I'm working with SMO now on a program which will extracts this kind of information

     

    Shortly, you have to instanciate in SMO a class Server and connects to the "master" database or your database ( that's easy you have the possibility to reuse a SqlConnection

     

    if your server class is called serverone

    in VC#

    DataBase db = serverone.DataBases["yourdbname"];

    // if you want , you may test before the existence of the database with

    if ( serverone.DataBases.Contains("yourdbname") )  

     

    serverone.DataBases return a DataBasesCollection ( it's why Contains may be used )

     

    see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database_members.aspx

     

    db.Roles return a DataBaseRolesCollection

    see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.databaserole_members.aspx

    foreach ( DataBaseRole role in DbRoles )

    {

    Console.WriteLine("Role : {0}",role.Name);

    }

     

    It's maybe too short for you but i prefer to give some informations you could use quickly

     

    Have a nice day

    Thursday, February 14, 2008 6:48 PM
  • Hello,

     

    I have obtained this :

     

    ConnectionState : True
    Number of DataBases : 12
    GestMedAlphaProd exists
    GestMedAlphaProd : 10 roles
     Role : db_accessadmin 0 members
     Role : db_backupoperator 1 members
        member : GestMed
     Role : db_datareader 1 members
        member : GestMed
     Role : db_datawriter 1 members
        member : GestMed
     Role : db_ddladmin 0 members
     Role : db_denydatareader 0 members
     Role : db_denydatawriter 0 members
     Role : db_owner 3 members
        member : dbo
        member : GestMed
        member : Paddraic
     Role : db_securityadmin 1 members
        member : GestMed
     Role : public 0 members
      User : dbo  ==> 1 roles
        role : db_owner
      User : GestMed  ==> 5 roles
        role : db_owner
        role : db_securityadmin
        role : db_backupoperator
        role : db_datareader
        role : db_datawriter
      User : guest  ==> 0 roles
      User : INFORMATION_SCHEMA  ==> 0 roles
      User : Paddraic  ==> 1 roles
        role : db_owner
      User : sys  ==> 0 roles
    **** User GestMed : 5 roles
        role : db_owner
        role : db_securityadmin
        role : db_backupoperator
        role : db_datareader
        role : db_datawriter

    ByeBye
    Appuyez sur une touche pour continuer...

     

    My code is :

     

    using System;

    using System.Collections.Generic;

    using System.Collections.Specialized;

    using System.Data.SqlClient;

    using System.Linq;

    using System.Text;

    using Microsoft.SqlServer.Management.Common;

    using Microsoft.SqlServer.Management.Smo;

    namespace Test_DatabaseRoles

    {

    class Program

    {

    static void Main(string[] args)

    {

    SqlConnectionStringBuilder bu = new SqlConnectionStringBuilder();

    bu.DataSource = @"CHAMBRE\SQLEXPRESS";

    //bu.InitialCatalog = "master"; if no InitialCatalog master is used

    bu.IntegratedSecurity = true;

    SqlConnection Conn = new SqlConnection();

    Conn.ConnectionString = bu.ConnectionString;

    ServerConnection serverconnection = new ServerConnection(Conn);

    Server server = new Server(serverconnection);

    server.ConnectionContext.Connect();

    Console.WriteLine("ConnectionState : {0}",server.ConnectionContext.IsOpen);

    Console.WriteLine("Number of DataBases : {0}",server.Databases.Count);

    if ( !server.Databases.Contains("GestMedAlphaProd") )

    {

    Console.WriteLine("GestMedAlphaProd does not exist");

    server.ConnectionContext.Disconnect();

    return;

    }

    Console.WriteLine("GestMedAlphaProd exists");

    DatabaseRoleCollection rolecoll = server.Databases["GestMedAlphaProd"].Roles;

    Console.WriteLine("GestMedAlphaProd : {0} roles",rolecoll.Count);

    foreach( DatabaseRole role in rolecoll )

    {

    Console.WriteLine(" Role : {0} {1} members",role.Name,role.EnumMembers().Count);

    StringCollection members = role.EnumMembers();

    foreach ( String str in members )

    {

    Console.WriteLine(" member : {0}",str);

    }

    }

    UserCollection usercoll = server.Databases["GestMedAlphaProd"].Users;

    foreach ( User user in usercoll )

    {

    StringCollection userrole = user.EnumRoles();

    Console.WriteLine(" User : {0} ==> {1} roles",user.Name,userrole.Count);

    foreach ( String str in userrole )

    {

    Console.WriteLine(" role : {0}",str);

    }

    }

    User user1 = server.Databases["GestMedAlphaProd"].Users["GestMed"];

    StringCollection userrole1 = user1.EnumRoles();

    Console.WriteLine("**** User GestMed : {0} roles",userrole1.Count);

    foreach (String str in userrole1)

    {

    Console.WriteLine (" role : {0}" , str);

    }

    server.ConnectionContext.Disconnect();

    Console.WriteLine("");

    Console.WriteLine("ByeBye");

    }

    }

    }

     

    I've no time to treat the exceptions.

    If your need it, i will post you

    I hope that will help you

     

    Have a nice day

     

    Correction : to be able to use SMO you have to add 4 references

    Microsoft.SqlServer.ConnectionInfo

    Microsoft.SqlServer.SMO

    Microsoft.SqlServer.SMOEnum

    Microsoft.Sqlserver.SqlEnum

    Microsoft.SqlServer.WMI    ( maybe unuseless now for your but i made a code snippet to have the 5 in one strike )

    Thursday, February 14, 2008 10:52 PM