CODEWITHSUNDEEP
t-sql
Alexander Stalt
Alexander Stalt

Reputation: 977

Check that user belongs to db role

I took some code here: Check if role consists of particular user in DB?

SELECT *
          FROM sys.database_role_members AS RM 
          JOIN sys.database_principals AS U 
            ON RM.member_principal_id = U.principal_id 
          JOIN sys.database_principals AS R 
            ON RM.role_principal_id = R.principal_id 
          WHERE U.name = 'operator1' 
            AND R.name = 'myrole1'

that query returns 1 row. It means that user 'operator1' belongs to role 'myrole1'. Now I'm trying to create a stored procedure for this:

CREATE PROCEDURE [dbo].[Proc1]
(
    @userName varchar,
    @roleName varchar
)
AS

IF EXISTS(SELECT * 
          FROM sys.database_role_members AS RM 
          JOIN sys.database_principals AS U 
            ON RM.member_principal_id = U.principal_id 
          JOIN sys.database_principals AS R 
            ON RM.role_principal_id = R.principal_id 
          WHERE U.name = @userName 
            AND R.name = @roleName)
            RETURN 0
ELSE RETURN -1

I use standard command from sql server 2008 'Execute stored procedure'

DECLARE @return_value int

EXEC    @return_value = [dbo].[Proc1]
        @userName = N'operator1',
        @roleName = N'myrole1'

SELECT  'Return Value' = @return_value

GO

it always returns -1. WHY ???

Upvotes: 1

Views: 2263

Answers (2)

Templar
Templar

Reputation: 5135

You need to define the length attribute for each of your procedure parameters. If you change the beginning of your stored procedure declaration to the following, it will return the correct response:

CREATE PROCEDURE [dbo].[Proc1]
(
    @userName varchar(255),
    @roleName varchar(255)
)
AS

Without the length attributes, SQL Server auto-defines the length of the variables as 1, so the user and role were being matched against "o" and "m", respectively. SQL Server is inconsistent in it's behavior of how it treats variables without the length specifier - sometimes they have a length of 30 and sometimes they have a length of 1. It is best practice to always specify the length attributes on string variables. See the following SQL Blog article for more information:

Upvotes: 2

user872744
user872744

Reputation:

This code is not good, because it can return false, if a user is member of a group, which then is member of a role.

User the built-in function IS_MEMBER() -- this is much simpler and always accureate.

Upvotes: 0

Related Questions