Solved

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

Posted on 2014-02-03
9
297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

728 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