Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)

share|improve this question

migrated from stackoverflow.com Mar 22 '13 at 8:51

This question came from our site for professional and enthusiast programmers.

    
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:

SELECT  1
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';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.