Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
    
Did you try removing the login from the role, and then re-adding? –  Jon Seigel Mar 22 '13 at 16:29
add comment

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

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

1 Answer

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
add comment

Your Answer

 
discard

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