How to programatically get database roles(Solved)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to programatically get database roles(Solved)

Author

Topic

biniman
Starting Member

6 Posts

Posted - 2012-04-09 : 19:13:57

Hello there,
I need to programmatically obtain the database names or the login names of users who are currently connected to my database. Once the users are identified, I need to know the database roles associated with each user so that I can enable or disable a user's ability to issue certain Insert, Delete or Update commands from a Visual Basic application. I have no idea about the system procudures or user-defined procedures to use to obtain the bits of information stated above. Can anyone out there help me?

Your suggestions would be highly appreciated.

Thanks.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-04-10 : 04:48:55

EXEC mydb.dbo.sp_helprolemember will return DBRole, membername, memberSID .

To view current connections use sp_who2

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

biniman
Starting Member

6 Posts

Posted - 2012-04-10 : 12:11:59

A million thanks. I have another challenge here. How do I redirect the output of the sp_helprolemember into a new table? I need to assign the values to variables and use them in my Visual Basic codes.

Once again, thanks for the suggestions.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-04-11 : 01:32:46

This will create a temp table - insert the output of dbo.sp_helprolemember

CREATE TABLE #tRolemember
(
strRoleName VARCHAR(50)
,strUserName VARCHAR(100)
,strUserID VARCHAR(100)
)
INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC dbo.sp_helprolemember

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

biniman
Starting Member

6 Posts

Posted - 2012-04-11 : 08:20:40

I cannot thank you enough. I eventually achieved the aim by creating a table from the following select statement:

Select t.name, t.principal_id, t.type_desc, t.sid, y.name As role_name
From sys.database_principals t Inner Join (Select m.name, r.role_principal_id, r.member_principal_id
From sys.database_principals m Inner Join sys.database_role_members r
ON m.principal_id = r.role_principal_id) As y
ON t.principal_id = y.member_principal_id
Order By (Right(SUSER_SNAME(t.sid), LEN(SUSER_SNAME(t.sid)) - CharIndex('\', SUSER_SNAME(t.sid))))

It looks like I have a long way to go on SQL, but with the supports from you guys, I think I will make it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources