Migrating the access permissions...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Migrating the access permissions...

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2004-05-14 : 09:42:19

How to migrate the access rights with the db? Since logins are on the server level...

------------------------
I think, therefore I am

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-14 : 13:37:02

Migrate them to where? When you restore a database on another server, permissions come across.

I've got a stored procedure that grants permissions. Search the forums for isp_Grant_Permissions. It grants EXEC on all stored procs. It wouldn't help migrate your permissions, but if all you want is EXEC on stored procs like you should have, then this would be handy.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-14 : 14:04:54

If you are moving a database to another server, you will need to recreate the logins. You can take a look at the scripts below and possibly modify them. I use them for server migrations to migrate logins. If you run this after you restore or attach the moved databases, it sets the logins for you and you're ready to go.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_help_revlogin @login_name
sysname = NULL
AS
--Declare needed variables.
DECLARE
@name sysname,
@xstatus int,
@binpwd varbinary (256),
@txtpwd sysname,
@tmpstr varchar (256),
@SID_varbinary varbinary(85),
@SID_string varchar(256),
@dbname varchar(255)
--Determine whether to process one login or all. Set up cursor accordingly.
IF (@login_name IS NULL)
BEGIN
DECLARE login_curs CURSOR FOR
SELECT
sxl.sid,
sxl.name,
sxl.xstatus,
sxl.password,
sd.name AS dbname
FROM
master..sysxlogins sxl
INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
WHERE
sxl.srvid IS NULL
AND sxl.name <> 'sa'
END
ELSE
BEGIN
DECLARE login_curs CURSOR FOR
SELECT
sxl.sid,
sxl.name,
sxl.xstatus,
sxl.password,
sd.name AS dbname
FROM
master..sysxlogins sxl
INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
WHERE
sxl.srvid IS NULL
AND sxl.name <> @login_name
END
OPEN login_curs
FETCH NEXT FROM login_curs
INTO
@SID_varbinary,
@name,
@xstatus,
@binpwd,
@dbname
--If no logins found, exit the procedure.
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SELECT @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SELECT @tmpstr =
'** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE
BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication

IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password

EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
BEGIN
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
END
ELSE
BEGIN
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
END

PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr =
'EXEC master..sp_addlogin ''' + @name + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE
BEGIN
BEGIN

-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr =
'EXEC master..sp_addlogin ''' + @name + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
END
IF (@xstatus & 2048) = 2048
BEGIN
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
PRINT @tmpstr
END
ELSE
BEGIN
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
--Add the default database.
SET @tmpstr = 'EXEC master..sp_defaultdb ''' + @name + ''',''' + @dbname + ''''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs
INTO
@SID_varbinary,
@name,
@xstatus,
@binpwd,
@dbname
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0

GO

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2004-05-17 : 03:53:19

Will the above script asign db userids to proper login? I heard that there is a mismatch of SID when migrating logins accross servers and pwd is assigned blank?

What are orphan users?

------------------------
I think, therefore I am

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-17 : 08:52:13

Yes. This one was rewrote to assign the proper SID. Just make sure you run it AFTER the databases are restored.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Gradwohk
Starting Member

6 Posts

Posted - 2004-08-17 : 08:21:51

marvellous!!!

Many thanks!
Karl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources