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.)

How to delete Guest user, via group membership

Dear all
In my databases I can see the user Guest, via group membership, but I
can not delete it by any way. Is there any way to delete it?
RegardsHi
What does it mean via group membership?
Do you have permissions to drop logins?
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>|||delete the row from sysusers table.
shahdharti@.gmail.com wrote:
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards|||ch
It is really deprecate to edit system tables
"ch" <ch@.dontemailme.com> wrote in message
news:43A16AB2.932AD7A0@.dontemailme.com...
> delete the row from sysusers table.
>
> shahdharti@.gmail.com wrote:
>> Dear all
>> In my databases I can see the user Guest, via group membership, but I
>> can not delete it by any way. Is there any way to delete it?
>> Regards|||In Enterprise manager when I see users list I see the Guest user and in
status column I see 'via group membership'.
In sysusers system table I can found guest user.
sp_helpuser does not show guest user.
When I try to delete I get error :15008 user Guest does not exist in
the current database.
Regards|||Hi
Why would you want to delete a rows from system table?
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegroups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>|||What version? 2000 or 2005?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>|||SQL Server 2000, SP3.
One more strange matter , when I register server in my coleagues PC ,
it is not showing Guest user there.
On my pc all servers which are registered I am seeing Guest user,
having status via group memeber ship.
I want to remove this guest for all servers from my PC.
Regards|||The user doesn't really exists in the real sense. Try
SELECT * FROM sysusers WHERE name = 'guest'
You will see that the column hasdbaccess is 0.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegroups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>|||Have you got the SQL2005 tools installed? The version of SQLDMO installed by
the SQL2005 tools issues a slightly different query to enumerate database
users than the SQL2000 versions. This has the side effect of the guest user
showing up in all databases when viewed via Enterprise Manager. If you open
EM on the server itself (assuming there are no SQL2005 components installed)
you should see that it doesn't show up (the default SQL2000 behaviour). This
is just a UI issue and nothing has actually changed on the server - it's
nothing to worry about.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>|||Hi Jasper!
Thanks for that! I thought I was going crazy when looking at this (for this posts sake) and noticed
that guest show up in each database even if it "doesn't exist". I was close to dead certain that
this isn't the normal behavior... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23wGk9QaAGHA.3872@.TK2MSFTNGP12.phx.gbl...
> Have you got the SQL2005 tools installed? The version of SQLDMO installed by the SQL2005 tools
> issues a slightly different query to enumerate database users than the SQL2000 versions. This has
> the side effect of the guest user showing up in all databases when viewed via Enterprise Manager.
> If you open EM on the server itself (assuming there are no SQL2005 components installed) you
> should see that it doesn't show up (the default SQL2000 behaviour). This is just a UI issue and
> nothing has actually changed on the server - it's nothing to worry about.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> <shahdharti@.gmail.com> wrote in message
> news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
>> Dear all
>> In my databases I can see the user Guest, via group membership, but I
>> can not delete it by any way. Is there any way to delete it?
>> Regards
>|||Thanks Jasper
You are right. Its a relief for me.
Regards|||jasper, you should have also posted this response in my thread titled
"how did guest get back into all of my databases?" ;)
thanks for the info.
Jasper Smith wrote:
> Have you got the SQL2005 tools installed? The version of SQLDMO installed by
> the SQL2005 tools issues a slightly different query to enumerate database
> users than the SQL2000 versions. This has the side effect of the guest user
> showing up in all databases when viewed via Enterprise Manager. If you open
> EM on the server itself (assuming there are no SQL2005 components installed)
> you should see that it doesn't show up (the default SQL2000 behaviour). This
> is just a UI issue and nothing has actually changed on the server - it's
> nothing to worry about.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> <shahdharti@.gmail.com> wrote in message
> news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> > Dear all
> >
> > In my databases I can see the user Guest, via group membership, but I
> > can not delete it by any way. Is there any way to delete it?
> >
> > Regards
> >

How to delete Guest user, via group membership

Dear all
In my databases I can see the user Guest, via group membership, but I
can not delete it by any way. Is there any way to delete it?
RegardsHi
What does it mean via group membership?
Do you have permissions to drop logins?
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>|||delete the row from sysusers table.
shahdharti@.gmail.com wrote:
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards|||ch
It is really deprecate to edit system tables
"ch" <ch@.dontemailme.com> wrote in message
news:43A16AB2.932AD7A0@.dontemailme.com...[vbcol=seagreen]
> delete the row from sysusers table.
>
> shahdharti@.gmail.com wrote:|||In Enterprise manager when I see users list I see the Guest user and in
status column I see 'via group membership'.
In sysusers system table I can found guest user.
sp_helpuser does not show guest user.
When I try to delete I get error :15008 user Guest does not exist in
the current database.
Regards|||Hi
Why would you want to delete a rows from system table?
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegroups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>|||What version? 2000 or 2005?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>|||SQL Server 2000, SP3.
One more strange matter , when I register server in my coleagues PC ,
it is not showing Guest user there.
On my pc all servers which are registered I am seeing Guest user,
having status via group memeber ship.
I want to remove this guest for all servers from my PC.
Regards|||The user doesn't really exists in the real sense. Try
SELECT * FROM sysusers WHERE name = 'guest'
You will see that the column hasdbaccess is 0.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegroups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>|||Have you got the SQL2005 tools installed? The version of SQLDMO installed by
the SQL2005 tools issues a slightly different query to enumerate database
users than the SQL2000 versions. This has the side effect of the guest user
showing up in all databases when viewed via Enterprise Manager. If you open
EM on the server itself (assuming there are no SQL2005 components installed)
you should see that it doesn't show up (the default SQL2000 behaviour). This
is just a UI issue and nothing has actually changed on the server - it's
nothing to worry about.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>

How to delete Guest user, via group membership

Dear all
In my databases I can see the user Guest, via group membership, but I
can not delete it by any way. Is there any way to delete it?
Regards
Hi
What does it mean via group membership?
Do you have permissions to drop logins?
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegr oups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>
|||delete the row from sysusers table.
shahdharti@.gmail.com wrote:
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
|||ch
It is really deprecate to edit system tables
"ch" <ch@.dontemailme.com> wrote in message
news:43A16AB2.932AD7A0@.dontemailme.com...[vbcol=seagreen]
> delete the row from sysusers table.
>
> shahdharti@.gmail.com wrote:
|||In Enterprise manager when I see users list I see the Guest user and in
status column I see 'via group membership'.
In sysusers system table I can found guest user.
sp_helpuser does not show guest user.
When I try to delete I get error :15008 user Guest does not exist in
the current database.
Regards
|||Hi
Why would you want to delete a rows from system table?
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegr oups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>
|||What version? 2000 or 2005?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegr oups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>
|||SQL Server 2000, SP3.
One more strange matter , when I register server in my coleagues PC ,
it is not showing Guest user there.
On my pc all servers which are registered I am seeing Guest user,
having status via group memeber ship.
I want to remove this guest for all servers from my PC.
Regards
|||The user doesn't really exists in the real sense. Try
SELECT * FROM sysusers WHERE name = 'guest'
You will see that the column hasdbaccess is 0.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegr oups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>
|||Have you got the SQL2005 tools installed? The version of SQLDMO installed by
the SQL2005 tools issues a slightly different query to enumerate database
users than the SQL2000 versions. This has the side effect of the guest user
showing up in all databases when viewed via Enterprise Manager. If you open
EM on the server itself (assuming there are no SQL2005 components installed)
you should see that it doesn't show up (the default SQL2000 behaviour). This
is just a UI issue and nothing has actually changed on the server - it's
nothing to worry about.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegr oups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>