Re: how to find out if a domain user is in the sysadmin server role?

The example in Books Online works for me. I connected with a login that is a member of a group that is a sysadmin. The user has no individual login. The code:
IF IS_SRVROLEMEMBER ('sysadmin') = 1
print 'Current user''s login is a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0
print 'Current user''s login is NOT a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL
print 'ERROR: The server role specified is not valid.'
Returns "Current user's login is a member of the sysadmin role."

Or if you are testing another user
EXECUTE AS LOGIN = 'DOMAIN\User';

IF IS_SRVROLEMEMBER ('sysadmin') = 1
print 'Current user''s login is a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0
print 'Current user''s login is NOT a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL
print 'ERROR: The server role specified is not valid.'

--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"Shy Engelberg" <Shy Engelberg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:4005638A-6F70-48CF-9EE7-C15B392580FB@xxxxxxxxxxxxxxxx
hi,
i am trying to find out using T-SQL if a domain user has sysdmin permssions.
i can use IS_SRVROLEMEMEBR, but if the user is not a login and directly
under the sysadmin role, this function will not help.
i need to know even if one of the domain or local groups the user is member
of has the sysadmin server role.
Example: if a user is a domain admin, he is a part of the server's local
admin group, which s a part of the BUILTIN\ADMINISTRATORS login, that has
sysadmin permissions.
Thanks,
Shy.

.

Relevant Pages

  • Re: SQL Login
    ... The sa login account is a member of that role as well. ... Dejan Sarka, SQL Server MVP ... > But how do we add the User X to the sysadmin / ...
    (microsoft.public.sqlserver.security)
  • Re: Other way of Changing Password if does not have Server Role
    ... A user can change his password without being a member of the sysadmin role. ... You just have to login as the user and then omit the 3rd parameter ... Dejan Sarka, SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: sp_change_users_login use after restore
    ... The user was a member of the sysadmin role, once I took the role out, ... In that case the login will will be 'dbo' in the ... > SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: Check User in Role?
    ... print 'Current user''s login is NOT a member of the sysadmin role' ... Software Consultant ...
    (microsoft.public.sqlserver.programming)
  • Re: sp_revoke login is not working as expected.
    ... EXEC xp_logininfo 'MyDomain\SomeUserAccount','members' ... Try specifying a group member rather than the group. ... This should list the Windows groups the user can connect with. ... connect with the non-existing login. ...
    (microsoft.public.sqlserver.security)