Script login/role-user mappings, login/object mapp

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 2005 Forums

SQL Server Administration (2005)

Script login/role-user mappings, login/object mapp

Author

Topic

newuser001
Starting Member

19 Posts

Posted - 2009-05-11 : 17:47:28

1. Script login/role-user mappings to user.sql
2. Script login/object mappings to objects.sql
3. Script T-SQL create for DB to db_create.sql
I want to generate script for the above there steps, instead of right click All Task generate the scripts

I want command to generate scripts in a folder by running the job
Some thing like OSQL Command...
Ex: osql -S TestServer -E -Q "exec master.dbo.sp_help_revlogin" -w 255 -o "C:\folder\TestServer_logins.sql"

I can't see the sp_help_revlogin and sp_hexadecimal in all SQL2000 Server
I want to get the Login information for selected databases

How about SQL Server 2005? The tables r different I cant find sysxlogins

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-11 : 18:13:32

sp_help_revlogin is not a built-in procedure, you have to add it to your servers:

http://support.microsoft.com/kb/246133 --SQL Server 2000
http://support.microsoft.com/kb/918992 --SQL Server 2005

Systems tables have been replaced with views in SQL 2005, and many new management views have been added. Books Online has details on all of the new views.

newuser001
Starting Member

19 Posts

Posted - 2009-05-11 : 18:19:02

Thanks a lot for information..

i want to script out database users and permissions for individual objects

I want to Scripts the below steps through command or .... in a job that should be save in folder , i do these steps manually by
Right Click on each database-> Generate Scripts -> and select required options to genetare these scripts.

1. Script login/role-user mappings
2. Script login/object mappings

3. Script T-SQL create for DB

now i want generate through a Job. and want to do this task for selected databases..

Any Idea..??

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-05-11 : 22:14:26

See my script:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111918

chorleyt
Starting Member

1 Post

Posted - 2009-07-10 : 12:42:55

quote:
Originally posted by newuser001

1. Script login/role-user mappings to user.sql
2. Script login/object mappings to objects.sql
3. Script T-SQL create for DB to db_create.sql
I want to generate script for the above there steps, instead of right click All Task generate the scripts

I want command to generate scripts in a folder by running the job
Some thing like OSQL Command...
Ex: osql -S TestServer -E -Q "exec master.dbo.sp_help_revlogin" -w 255 -o "C:\folder\TestServer_logins.sql"

I can't see the sp_help_revlogin and sp_hexadecimal in all SQL2000 Server
I want to get the Login information for selected databases

How about SQL Server 2005? The tables r different I cant find sysxlogins

Hello All

sp_help_revlogin is as fine as it goes; but a bit of extra tweaking of the script will also ensure you get the role memberships as well.

Add the following variables before DECLARE @defaultdb: -
DECLARE @role_bulkadmin varchar (1)
DECLARE @role_dbcreator varchar (1)
DECLARE @role_diskadmin varchar (1)
DECLARE @role_processadmin varchar (1)
DECLARE @role_securityadmin varchar (1)
DECLARE @role_serveradmin varchar (1)
DECLARE @role_setupadmin varchar (1)
DECLARE @role_sysadmin varchar (1)

Next alter the select statements in the DECLARE login_curs CURSORS: -

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin,
l.bulkadmin,l.dbcreator,l.diskadmin,l.processadmin,l.securityadmin,l.serveradmin,l.setupadmin,l.sysadmin
FROM sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin,
l.bulkadmin,l.dbcreator,l.diskadmin,l.processadmin,l.securityadmin,l.serveradmin,l.setupadmin,l.sysadmin
FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

Now alter the FETCH NEXT sections: -

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin,@role_bulkadmin,@role_dbcreator,@role_diskadmin,@role_processadmin,@role_securityadmin,@role_serveradmin,@role_setupadmin,@role_sysadmin

Finally find the PRINT @tmpstr line and then add the following after the "END" proceeding @tempstr: -

If @role_bulkadmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' + , @rolename = N + '''' + bulkadmin + '''' '
END
IF @role_dbcreator = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' + , @rolename = N + '''' + dbcreator + '''' '
END
IF @role_diskadmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' + , @rolename = N + '''' + diskadmin + '''' '
END
IF @role_processadmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' + , @rolename = N + '''' + processadmin + '''' '
END
IF @role_securityadmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' + , @rolename = N + '''' + securityadmin + '''' '
END
IF @role_serveradmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' + , @rolename = N + '''' + serveradmin + '''' '
END
IF @role_setupadmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' + , @rolename = N + '''' + setupadmin + '''' '
END
IF @role_sysadmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' + , @rolename = N + '''' + sysadmin + '''' '
END
PRINT @tmpstr

Regards

Tony C

mhunady
Starting Member

1 Post

Posted - 2011-01-14 : 04:19:25

Hi,
there was some mistakes in previous script. here is full script for login (sql,NT) migration:

USE [master]
GO

/****** Object: StoredProcedure [dbo].[sp_hexadecimal] Script Date: 01/14/2011 10:17:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_hexadecimal] @binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
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

USE [master]
GO

/****** Object: StoredProcedure [dbo].[sp_help_revlogin] Script Date: 01/14/2011 10:17:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @role_bulkadmin varchar (1)
DECLARE @role_dbcreator varchar (1)
DECLARE @role_diskadmin varchar (1)
DECLARE @role_processadmin varchar (1)
DECLARE @role_securityadmin varchar (1)
DECLARE @role_serveradmin varchar (1)
DECLARE @role_setupadmin varchar (1)
DECLARE @role_sysadmin varchar (1)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin,
l.bulkadmin,l.dbcreator,l.diskadmin,l.processadmin,l.securityadmin,l.serveradmin,l.setupadmin,l.sysadmin
FROM sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin,
l.bulkadmin,l.dbcreator,l.diskadmin,l.processadmin,l.securityadmin,l.serveradmin,l.setupadmin,l.sysadmin
FROM sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin,@role_bulkadmin,@role_dbcreator,@role_diskadmin,@role_processadmin,@role_securityadmin,@role_serveradmin,@role_setupadmin,@role_sysadmin

IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
If @role_bulkadmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' , @rolename = N''bulkadmin'' '
PRINT @tmpstr
END
IF @role_dbcreator = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' , @rolename = N''dbcreator'' '
PRINT @tmpstr
END
IF @role_diskadmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' , @rolename = N''diskadmin'' '
PRINT @tmpstr
END
IF @role_processadmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' , @rolename = N''processadmin'' '
PRINT @tmpstr
END
IF @role_securityadmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' , @rolename = N''securityadmin'' '
PRINT @tmpstr
END
IF @role_serveradmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' , @rolename = N''serveradmin'' '
PRINT @tmpstr
END
IF @role_setupadmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' , @rolename = N''setupadmin'' '
PRINT @tmpstr
END
IF @role_sysadmin = '1'
BEGIN
SET @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''' , @rolename = N''sysadmin'' '
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin,@role_bulkadmin,@role_dbcreator,@role_diskadmin,@role_processadmin,@role_securityadmin,@role_serveradmin,@role_setupadmin,@role_sysadmin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0

GO

muhady

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources