RE: How to Determine if a user is a member of the System Admin role?

Hello Brad,

As for a windows user account(or group) or a sqlserver account, before we
check if it is of sysadmin role (in the server instance), we should first
check if it's a server login(principal) on that server instance. For this,
we can use some T-SQL query to lookup all the principals of sysadmin role
in the master db. It'll be a bit different for SQL 2005 and SQL 2000:


============2005===========
select p1.Name as Role_name, p2.Name as Member_name from
sys.server_role_members r1 inner join sys.server_principals p1
on r1.Role_principal_id = p1.Principal_id

inner join sys.server_principals p2
on r1.Member_principal_id = p2.Principal_id
=========================

As you can see, we need to query multiple catalog views in sys schema.


While in SQL server 2000, we can diretly query the "syslogins" table in
master db, and this table contains a "sysadmin" column indicate whether the
certain principal is of sysadmin role.
==============2000==================
select * from syslogins



Hope this helps.

Regards,

Steven Cheng
Microsoft Online Community Support


==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.



Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

.

Relevant Pages

  • Re: Baseline Security Analyzer advice locked me out
    ... Hi Gentry, ... I am a developer and not a SQL Admin. ... >> could no longer add BUILTIN\Administrators back into the Sysadmin role. ...
    (microsoft.public.sqlserver.security)
  • RE: DTS is blocked by XP
    ... When usung XP (extended procedures), the login you use to access the SQL ... server instance will require permissions within to run extended procedures. ... Look for setting permissiones in SQL. ...
    (microsoft.public.sqlserver.dts)
  • Re: System Admin Role
    ... The SysAdmin role is instance wide. ... In SQL Server 2000, to use SQL Server Profiler, one has to be in the ...
    (microsoft.public.sqlserver.security)
  • Re: replication between 64bit and 32bit
    ... In SQL 2008 there isn't really transformable subscriptions so this isn't an ... the 64-bit and 32-bit environments. ... A backup created on a server instance running ...
    (microsoft.public.sqlserver.replication)
  • Re: Securing MSSQL
    ... Can someone copy the mdf/ldf and try to "restore" the DB from these files? ... and just leave yourself in the sysadmin role. ... expand Security under the SQL instance --> expand ...
    (microsoft.public.sqlserver.server)