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

Is there a script/function that can be used to determine if a user (granted
login/access via NT Group Membership) is a member of the System Administrato
r
group?
We had an issue where a user was a member of multiple NT Global Groups, one
of which was a member of (had) the System Admin role. Our application check
s
to see if the NT Group for our Application has DBO rights, but this returned
false ... yet the user would (by default) create objects (views/tables) in
dbo. We finally traced this down via Enterprise Mgr, Security, Server Roles
and dbl-clicked "System Administrators" and found that there were unexpected
groups there, and our user(s) were in one or more of these groups. How can w
e
determine this via code/script and then "turn it off" for our database (of
course it is possible that a user that is a member of another group MAY need
SA rights in another database)?
Thank you,
Brad
--
Brad Ashforth> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
SELECT IS_SRVROLEMEMBER('sysadmin')

> How can we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
In SQL 2000, there are only 2 cases where objects will be created in the dbo
schema by default: 1) user is the database owner and 2) user is a
sysadmin role member. The query 'SELECT USER' will return 'dbo' in both
cases.
I'm not sure I understand what you mean by 'turn it off'. Do you mean that
you want the default schema to be other than 'dbo' for the dbo user? Have
you considered schema-qualifying object names so that the default schema
isn't relevant?
Hope this helps.
Dan Guzman
SQL Server MVP
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:5BEE8141-69CC-415A-A57E-48C875CB31AE@.microsoft.com...
> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
> We had an issue where a user was a member of multiple NT Global Groups,
> one
> of which was a member of (had) the System Admin role. Our application
> checks
> to see if the NT Group for our Application has DBO rights, but this
> returned
> false ... yet the user would (by default) create objects (views/tables) in
> dbo. We finally traced this down via Enterprise Mgr, Security, Server
> Roles
> and dbl-clicked "System Administrators" and found that there were
> unexpected
> groups there, and our user(s) were in one or more of these groups. How can
> we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
> Thank you,
> Brad
> --
> Brad Ashforth|||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.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
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.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
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.)