Solved

How to get my database users in new database without a restore?

Posted on 2014-02-03
9
288 Views
Last Modified: 2014-02-06
Experts,

We are in the process of doing a hardware refresh on two sites. This is new hardware and a vanilla installation on new operating system (Server 2012) and new version of SQL 2012 etc. What I need to know is what is the easiest way to import user accounts (along with all the rights and permissions) from my "database" and export these to new SQL 2012 database? I do not want to do this manually as we are talking about couple of hundred accounts for each site.  My current version of SQL is 2005.

I was able to use a script to export/import SQL logins but how can I do the same thing for the database? All I want is the user accounts, rights and permissions.

Thanks!
0
Comment
Question by:CervisTECH
  • 4
  • 4
9 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39829653
See the following link for a script that you can point at a DB:
http://gallery.technet.microsoft.com/scriptcenter/Get-logins-databases-816f66b2
0
 

Author Comment

by:CervisTECH
ID: 39829735
Kyle, How can I get the results from my SQL 2005 database to my SQL 2012 database?  The script gives me the information but I am not sure how to migrate the results.

Thanks!
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39829800
you can follow the below KB article to get this done...
it works for 2005 -> 2012 scenario as well

http://support.microsoft.com/kb/918992/en-us
0
 

Author Comment

by:CervisTECH
ID: 39829828
Surendra, this solution is ok if I want to grab the sql logins, I am trying to grab the database users, roles and permissions without doing a database restore. This script will not work on the database itself.

Thanks!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 39830075
actually, this one will do it (ti's from 2000 to 2005 but should still work going from 2005 to 2012):

http://support.microsoft.com/kb/246133  set @include_db = 1, @include_role = 1


or you could do something similair.  Essentially generate the dynamic sql and exec it per role.
0
 

Author Comment

by:CervisTECH
ID: 39830775
Kyle, again this solution is ok if I want to grab the sql logins, I am trying to grab the database users, roles and permissions without doing a database restore. This script will not work on the database itself.

Thanks!
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39833146
Cervis . . . look again.  It has the options to include the DBs and roles.
0
 

Author Comment

by:CervisTECH
ID: 39835219
This script still doesn't work I ran it and it only grabbed the sql logins and security for SQL not the database. If you think I am missing something could you show me the part of the script I should be running just for the database? I've already migrated the SQL logins and security I need to just do the same thing on a database level.

Thanks!
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39835805
Sorry about that.  Saw the roles and forgot about the default server ones.

Next try:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6d9c7fe0-29cf-40b2-ad4b-28a9a3098200/how-to-script-out-existing-database-users-with-role-membership-and-explicit-object-permissions-for

•In order to script the database specific permissions per user you can use the following script:
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_help_revdbrole]    Script Date: 06/01/2012 10:30:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[sp_help_revdbrole]
	@login_name varchar(100) = NULL
AS
BEGIN
	SET NOCOUNT ON 
	
	DECLARE @login_sid varbinary(85)
	DECLARE @login varchar(100)
	IF (@login_name IS NULL)
	BEGIN
		DECLARE login_curs CURSOR FOR
		select sid,name from sys.server_principals where type IN ('G','U','S') and name <> 'sa'
	END
	ELSE
	BEGIN
		IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @login_name AND type IN ('G','U','S'))
		BEGIN 
				  PRINT 'Invalid login ' + @login_name + 'Please input valid login name'
				  RETURN
		END
		
		DECLARE login_curs CURSOR FOR
		select sid,name from sys.server_principals where type IN ('G','U','S') and name <> 'sa' and name = @login_name
	END

	OPEN login_curs
	FETCH NEXT FROM login_curs INTO @login_sid,@login
	
	WHILE (@@FETCH_STATUS != -1)
	BEGIN 
	
		DECLARE @maxid int
		IF OBJECT_ID('tempdb..#db_users') is not null
		DROP TABLE #db_users 
		SELECT id = identity(int,1,1), sql_cmd = 'SELECT '''+name+''', * FROM ['+name+'].sys.database_principals' INTO #db_users FROM sys.sysdatabases 
		SET @maxid = @@ROWCOUNT
		 
		---------------------------------------------------
		--Find out list of db that the login has access to 
		---------------------------------------------------
		 
		IF OBJECT_ID('tempdb..#alldb_users') is not null
		DROP TABLE #alldb_users 
		 
		CREATE TABLE #alldb_users(
				  [dbname] [sysname] NOT NULL,
				  [name] [sysname] NOT NULL,
				  [principal_id] [int] NOT NULL,
				  [type] [char](1) NOT NULL,
				  [type_desc] [nvarchar](60) NULL,
				  [default_schema_name] [sysname] NULL,
				  [create_date] [datetime] NOT NULL,
				  [modify_date] [datetime] NOT NULL,
				  [owning_principal_id] [int] NULL,
				  [sid] [varbinary](85) NULL,
				  [is_fixed_role] [bit] NOT NULL
		) 
		 
		DECLARE @id int, @sqlcmd varchar(500)
		SET @id = 1 
		WHILE @id <=@maxid
		BEGIN 
				  SELECT @sqlcmd = sql_cmd FROM #db_users WHERE id = @id 
				  INSERT INTO #alldb_users EXEC (@sqlcmd)
				  SET @id = @id + 1 
		END
		 
		DELETE FROM #alldb_users WHERE sid is null 
		DELETE FROM #alldb_users WHERE sid <> @login_sid
		 
		--SELECT * FROM #alldb_users
		----------------------------------------------
		--granting database role to login 
		----------------------------------------------
		PRINT ''
		PRINT '----------------------------------------------'
		PRINT '--Grant database role to login ' + @login
		PRINT '----------------------------------------------'
		 
		 
		IF OBJECT_ID('tempdb..#dbrole') is not null
		DROP TABLE #dbrole
		 
		create table #dbrole (dbname varchar(100), dbrole varchar (100), dbrole_member varchar(100), 
			sid varbinary(85), default_schema_name varchar(100), login_name varchar(100), db_principal_id int)
		DECLARE @dbrole_sqlcmd varchar(max)
		SET @dbrole_sqlcmd = ''
		SELECT @dbrole_sqlcmd = @dbrole_sqlcmd + 
			'SELECT '''+dbname+''', c.name, b.name, b.sid, b.default_schema_name, d.name, b.principal_id as login_name 
		from ['+dbname+'].sys.database_role_members a 
		inner join ['+dbname+'].sys.database_principals b on a.member_principal_id = b.principal_id
		inner join ['+dbname+'].sys.database_principals c on a.role_principal_id = c.principal_id
		left join sys.server_principals d on b.sid = d.sid
		'
		from #alldb_users 
		--SELECT @dbrole_sqlcmd
		--PRINT @dbrole_sqlcmd
		INSERT INTO #dbrole exec(@dbrole_sqlcmd)
		--SELECT * FROM #dbrole
		 
		DELETE FROM #dbrole WHERE sid <> @login_sid
		 
		ALTER TABLE #dbrole ADD ID INT identity(1,1)
		 
		DECLARE @counter int, @maxid2 int, @login_dbrole varchar(max) 
		SELECT @maxid2 = MAX(ID) FROM #dbrole
		SET @counter = 1
		 
		--SELECT * FROM #dbrole 
		 
		IF NOT EXISTS (SELECT * FROM #dbrole )
		BEGIN 
				  PRINT '--Login ['+@login+'] is not a member of any database level role'
				  --return 
		END
		
		declare @dbname varchar(100) 
		declare @dbrole_member varchar(100) 
		declare @dbrole varchar(100) 
		declare @loginname varchar(100) 
		declare @default_schema_name varchar(100) 
		set @login_dbrole = ''

		declare roles_cursor cursor for 
		select dbname,dbrole,dbrole_member,login_name,default_schema_name from #dbrole
		open roles_cursor
		FETCH NEXT FROM roles_cursor INTO @dbname,@dbrole,@dbrole_member,@login_name,@default_schema_name
		
		while (@@fetch_status=0)
		begin
			print '
				USE ['+@dbname+']
				IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '''+@dbrole_member+''')
				BEGIN 
						  CREATE USER ['+@dbrole_member+'] 
								FOR LOGIN ['+@login_name+']'+isnull(' WITH DEFAULT_SCHEMA=['+@default_schema_name+']','')+'
				END
				ALTER USER ['+@dbrole_member+'] WITH LOGIN = ['+@login_name+']
				EXEC sp_addrolemember '''+@dbrole+''','''+@dbrole_member+'''
				 
				'
			FETCH NEXT FROM roles_cursor INTO @dbname,@dbrole,@dbrole_member,@login_name,@default_schema_name
		end
		close roles_cursor
		deallocate roles_cursor
		--PRINT @login_dbrole
/*
		WHILE @counter <= @maxid2
		BEGIN 
		SELECT @login_dbrole  = 'USE ['+dbname+']
		IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '''+dbrole_member+''')
		BEGIN 
				  CREATE USER ['+dbrole_member+'] 
						FOR LOGIN ['+login_name+']'+isnull(' WITH DEFAULT_SCHEMA=['+default_schema_name+']','')+'
		END
		ALTER USER ['+dbrole_member+'] WITH LOGIN = ['+login_name+']
		EXEC sp_addrolemember '''+dbrole+''','''+dbrole_member+'''
		 
		' FROM #dbrole WHERE ID = @counter
		  SET @counter = @counter + 1 
		  PRINT @login_dbrole 
		END 
*/		
		FETCH NEXT FROM login_curs INTO @login_sid,@login
	END -- WHILE
	CLOSE login_curs
	DEALLOCATE login_curs
END

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now