I am facing a weird issue with SQL Server 2012.

I have a user (say RS\sqluser) which has bulkadmin role.

Now when i run this query

Select IS_SRVROLEMEMBER('bulkadmin', 'RS\sqluser')

output is 0. But when i execute this query

sp_helpsrvrolemember 'bulkadmin'

I can see RS\sqluser present in the list.

Can someone please help me understand how is the output differing or is it BUG in SQL Server 2012? (Can't be a BUG as same query with different user works fine)

  • Did you try removing the login from the role, and then re-adding? – Jon Seigel Mar 22 '13 at 16:29

You could have problems whereby the DC is not available, the user is actually a Windows group, or you could possibly have UAC issues. It may even be a permissions issue in AD as regards the account (run xp_logininfo 'RS\sqluser' and see if you get an 0x5 error).

I prefer to directly query the system tables to get that sort of information:

FROM    sys.server_principals sp
        INNER JOIN sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
        INNER JOIN sys.server_principals sp2 ON srm.role_principal_id = sp2.principal_id
WHERE   sp2.name = 'bulkadmin'
        AND sp.name = 'RS\sqluser';
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy