[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-02-03
9
Medium Priority
?
302 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 41

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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 41

Accepted Solution

by:
Kyle Abrahams earned 1500 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 41

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 41

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

649 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