Home / server / Questions / 74802
In Process
Nasa
Asked: 2009-10-16 07:19:53 +0800 CST

SQL 2000 and group names

  • 772

I have a SQL 2000 server which has databases, under user section of the database object, I have some NT 4.0 groups. These groups were migrated over to Active Directory some time ago using ADMT with SID history. The original source domain groups have since been deleted. The access shown is olddomain\groupname. I don't know why, if they were ntfs permissions they would update automatically to target\groupname.

The users in the AD domain still have access to the database as they are a member of the migrated group (Target\groupname). I was wondering

1) Why does the old group (source\groupname) show up as it doesn't exist anymore. But access is still granted to the target group? 2) Is there any easy way to update the group name from source\groupname to target\groupname?

Thanks for any help.

windows

2 Answers

  1. 1) Why does the old group (source\groupname) show up as it doesn't exist anymore. But access is still granted to the target group?

    If you were to look at the SQL Server Login assigned to the database user in question, you would probably find that the login has the new domain name. SQL Server 2000 database users have two main purposes: (1) a database security context; and (2) a database schema name (in later versions, users and schemas are totally separate entities).

    2) Is there any easy way to update the group name from source\groupname to target\groupname?

    Technically speaking, while SQL Server 2000 does not provide a way to rename a database user directly, you can work around this limitation by doing the following:

    1. adding a new database user with the desired name (e.g. target\groupname);
    2. assigning the same roles and/or permissions to the new user as are assigned to the old user;
    3. changing the database user associated with the SQL Server login; and
    4. dropping the old database user (Warning: Before dropping the old user, I would highly recommend checking all source code and object dependencies. If in doubt, rather leave it where it is than break a perfectly working application).

    Everything described above can be performed directly through SQL Enterprise Manager, but if you need to script the operation, you can user the following stored procedures and Transact-SQL commands (see SQL Books Online for additional usage information):

    sp_adduser
sp_addrolemember
GRANT
sp_change_users_login
sp_dropuser
    • 1

  2. SQL puts an record for the user in the database's sysusers table when you grant a login access to the database. One of the columns in this table is name which, for Windows logins, is the domain\user_or_group_name of the account or group (depending on how exactly you do the db user mapping). Your old domain name is probably coming from that record in the sysusers table. Access is based on SID which is why it's still working.

    Run this query:

    select * from [dbname].dbo.sysusers
where name='olddomain\groupname'

    and look at the createdate column. I'm guessing it's going to be prior to the time that you migrated your domain.

    • 0