Re: 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
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@xxxxxxxxxxxxx> wrote in message
news:5BEE8141-69CC-415A-A57E-48C875CB31AE@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: User rights and XPe Component Database
    ... - On a PC, I installed the tools and SP2 update, database etc.. ... - I create a new local user on this machine, I called it XPeUser ... I add XpeUser as a member of the Administrators ... Of course he souldn't be administrator. ...
    (microsoft.public.windowsxp.embedded)
  • Re: Changing Table Owner and user Owner
    ... Database Owner (dbo) ... any object created by any member of the sysadmin fixed server role ...
    (microsoft.public.sqlserver.security)
  • Re: Permission to Users under DB
    ... public and db_owners database roles. ... The user mapped to dbo ... as being a member of db_owners role although the default ... permissions in the database will be the same. ...
    (microsoft.public.sqlserver.security)
  • Re: Default User
    ... a member of the db_owner Role. ... The dbo is a user that has implied permissions to perform all activities ... database is mapped to the special user inside each database called dbo. ... any object created by any member of the sysadmin fixed server role ...
    (microsoft.public.sqlserver.security)
  • Re: When creating a new table, owner is DBO I need another user
    ... database is mapped to the special user inside each database called dbo. ... any object created by any member of the sysadmin fixed server role ...
    (microsoft.public.sqlserver.security)