June 9, 2005 at 6:11 am
Hi everybody,
I have some question about SQL Server loginstrategies because I might not understand this right. Until now we just had some instances with databases having users authenticating at SQL Server level. That worked fine and after copying complete databases from production to test-environments I could resolve problems regarding sid's with sp_change_user_login.
Lately we are getting more instances/databases with domainaccounts authenticating at Windows level. In this way, I recently discovered, this sp_change_users_login to correct mismatches in sids' is not working. The common advise is to drop and recreate the users, which is difficult because some of them own objects.
So I started to read what might be the best implementation for security and handling logins. I read many advices like this:
1. Create a Domain Group (global group).
2. Create a local group on your server.
3. Add your domain group into the local group on your server. (global groups into local groups).
4. Add the local group (which was just populated with the global/domain group) in SQL.
5. Assign appropriate privilages to the local group that you just added to SQL under logins.
I'm trying to understand what's happening here. Is it right that in the end this local group is mapped to one SQL Server login and therefor every userconnection is using the same account in the database?
It seems to me that in this way there can't be any objectcreation via separate usernames and there is no way that I can trace one usersession, because of the one SQL Server login I will never know which user it really is.
Can somebody advise me about the best way handling this Windows authentication regarding global/local groups, having users create objects in their own schema and can I copy databases from production to test without having problems with sid's? (I don't wanna transfer logins, they already exists like t_account, a_account, p_account: I just have to copy a database and recreate the database users according to the environment?)
Thanks in advance,
Peter.
June 13, 2005 at 8:00 am
This was removed by the editor as SPAM
June 14, 2005 at 1:44 am
When I restore a database to another server I use the following stored procedures to rectify the user SID etc. - I forget which one should be run first..:
CREATE PROCEDURE dbo.Usp_fixusers
-- This procedure will link the User Names from a restored database to the user names in the SQL Server
AS
BEGIN
DECLARE @username varchar(25)
DECLARE fixusers CURSOR FOR
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers
END
GO
/*
The following precedure will create Local User Names (not domain names) from a restored database on the SQL Server
EXEC fix_logins @random='RANDOM', @run='TEST'
EXEC fix_logins @random='RANDOM', @run='RUN'
*/
CREATE proc dbo.Usp_fix_logins
@random varchar(20)='',
@run varchar(20)='TEST'
as
begin
set nocount on
declare @no_users int
declare @user_name sysname
declare @dbname sysname
declare @def_lang sysname
declare @counter int
declare @name sysname
declare @sid varbinary(85)
declare @PW varchar(20)
if (upper(@run) <> 'RUN')
begin
print ''
print '**************************'
print '* Test Run *'
print '**************************'
print ''
end
select @no_users = count(*)
from sysusers
where
sid <> 0x01 and
sid <> 0x00 and
sid is not NULL
select @dbname = db_name(dbid) from master..sysprocesses where spid=@@spid
select @def_lang =a.name
from master..syslanguages a, master..sysconfigures b
where
b.comment = 'default language' and
b.value = a.langid
select @counter=1
declare sysusers_cursor cursor for
select name, sid
from sysusers
where
sid <> 0x01 and
sid <> 0x00 and
sid is not NULL
order by name
open sysusers_cursor
fetch next from sysusers_cursor into @name, @sid
while (@@FETCH_STATUS = 0)
begin
select @counter=@counter + 1
if (upper(@random) = 'RANDOM')
begin
select @PW=convert(varchar(50),RAND(@counter))
select @PW=substring(@pw,3,5)
end
else
begin
select @PW='password'
end
if (upper(@run) = 'RUN')
begin
exec sp_addlogin @name, @PW, @dbname, @def_lang, @sid
end
select 'Adding Login: ', @name, @PW, @dbname, @def_lang, @sid
fetch next from sysusers_cursor into @name, @sid
end
close sysusers_cursor
deallocate sysusers_cursor
print 'master..syslogins:'
select name, sid from master..syslogins order by name
select @dbname=@dbname+'..sysusers'
print ''
print @dbname
select name, sid
from sysusers
where
sid <> 0x01 and
sid <> 0x00 and
sid is not NULL
order by name
set nocount off
end
GO
When it comes to group access, simply create a Domain Group, add you users to that group, and then add the Domain Group to the SQL Server user list. You do not need to create a local group on the server.
When users, who have access via group membership, log into the database, their access will appear as their own Username. If you use the "SQL Server Profiler", you'll see their own username access the database and not the Group Name.
I hope this helps...
Regards,
Nic Wahsington
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply