Re: Clean up Broken Aliases
From: John (Notme@yourmailbox.com)
Date: 02/26/03
- Next message: Sonny J: "Access front end Sql Server Backend Problem"
- Previous message: Sue Hoegemeier: "Re: Audit policy question with SQL 2000"
- In reply to: Dan Jameson: "Clean up Broken Aliases"
- Next in thread: Steve Thompson: "Re: Clean up Broken Aliases"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "John" <Notme@yourmailbox.com> Date: Tue, 25 Feb 2003 23:01:54 -0600
security <> securitytools. Pleas post to the right group.
"Dan Jameson" <djameson@childrensoncologygroup.org> wrote in message
news:e7r$H9R3CHA.1396@TK2MSFTNGP09.phx.gbl...
> Hi,
>
>
>
> I'm cleaning up excess logins on our SQL Server 2000 server. Several of
the
> databases started life as SQL Server 6.5 databases on a different server.
I
> have found several entries in the sysusers table of several databases that
> seem to be broken aliases. I have included some sample data from sysusers
> and sp_helpuser from one of these databases. As you can see, there are
two
> users in the sysusers data that have status = 16, a backslash in front of
> the name, and have isaliased = 1. You can also see that these two names
are
> not in the first record set from sp_helpuser and that there are two NULL
> aliases in the second record set from sp_helpuser. These two names cause
> sp_revokedbaccess and sp_dropalias to error-out on a bad name. Also, they
> do not match any server logins on name or SID.
>
> I see this pattern in several databases. Can I just give myself
permission
> to update system tables and delete these entries for these broken aliases?
> Or, is there a way to fix these aliases so they can be dropped with
> sp_dropalias?
>
>
> --
> Thank you,
> Daniel Jameson
> Children's Oncology Group
> http://www.childrensoncologygroup.org
>
>
>
>
>
> select
> uid, status, name, roles,
>
hasdbaccess,islogin,isntname,isntgroup,isntuser,issqluser,isaliased,issqlrol
> e,isapprole
> from sysusers
> where status > 0
> exec sp_helpuser
>
>
>
>
>
> uid status name roles hasdbaccess islogin isntname
> isntgroup isntuser issqluser isaliased issqlrole isapprole
>
> ------ ------ --------------- ------- ----------- -------- ----------- ---
-- > ------ ----------- ----------- ----------- ----------- ----------- > > 2 2 guest 0x00 1 1 0 0 > 0 1 0 0 0 > > 1 2 dbo 0x01 1 1 0 0 > 0 1 0 0 0 > > 6 6 COG\GOC.IS 0xC0 1 1 1 1 > 0 0 0 0 0 > > 12 16 \yNikiforova 0x00 0 1 0 0 > 0 0 1 0 0 > > 9 16 \fPerez 0x00 0 1 0 0 > 0 0 1 0 0 > > 46 2 webuser 0x00 1 1 0 0 > 0 1 0 0 0 > > > > (6 row(s) affected) > > > > UserName GroupName LoginName DefDBName UserID SID > > -------------- ----------------- ------------- ------------ ------ ------- -- > ---------------------------------------------------- > > COG\GOC.IS db_datareader COG\GOC.IS master 6 > 0x01050000000000051500XXXXXXXXXXXXXXXXXXXXXXXX636B84060000 > > COG\GOC.IS db_datawriter COG\GOC.IS master 6 > 0x01050000000000051500XXXXXXXXXXXXXXXXXXXXXXXX636B84060000 > > dbo db_owner sa master 1 0x01 > > guest public NULL NULL 2 0x00 > > webuser public webuser master 46 > 0xEA2C00XXXXXXXXXXXXXXXXXXXXXXXX91BF > > > > LoginName UserNameAliasedTo > > ---------------- ------------------- > > NULL dbo > > NULL dbo > > > >
- Next message: Sonny J: "Access front end Sql Server Backend Problem"
- Previous message: Sue Hoegemeier: "Re: Audit policy question with SQL 2000"
- In reply to: Dan Jameson: "Clean up Broken Aliases"
- Next in thread: Steve Thompson: "Re: Clean up Broken Aliases"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
