SQL Stored Procedure to get user role

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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Stored Procedure to get user role

Author

Topic

meisenstein
Starting Member

1 Post

Posted - 2010-07-12 : 05:56:28

Hi All

Hoping someone can help.

I would like to create a stored procedure in SQL server which I can call from an access front-end application to determine what the role of the logged in user is to allow me to enable / disable application controls based on roles.

I have the following code:

Option Compare Database

Dim myString As String

Function GetRole() As String

Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "sp_CheckRole"
End With

Set rs = cmd.Execute
GetRole = rs!Role

Set cmd = Nothing
Set rs = Nothing

End Function

Private Sub Form_Load()

myString = GetRole()

End Sub

What I would like to know is what is the syntax I should use in the stored procedure ( here i called it sp_CheckRole ) to pass the role back to the myString variable.

Any help would be greatly appreciated.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-12 : 11:08:05

Use an API to grab and pass in the user Windows login to stored procedure

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA"(ByVal lpBuffer As String, nSize As Long) As Long

you need both an input parameter and an output parameter for the "With cmd" section

You have to remember a user can have multiple roles so do you want all of them of just a specific one. It will affect how your sproc will look like. If you want all of them try following


create sproc GetRole(@strWindowsLogin varchar(50))
AS
SELECT * 
  FROM sys.server_principals 
  WHERE name = @strWindowsLogin

If you want just one role it will be different, you will have to specify it in sproc, what specifically are you looking for?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources