February 16, 2011 at 9:51 pm
Hello
I have a restore job that runs one of our servers and I have to script out all the permissions and restore them again for certain databases. I have written the script below which does the job fine, I just have to cut and paste the results into a file to use after the restore. However I need to automate this so I have tried to use bcp to export the results but keep running into sytax issues with the single quotes. Any help would be much appreciated
here is the BCP syntax which i'm trying to insert
select @SQL = 'bcp "<SQL Query>" -c -CACP -T -t","'
EXEC @result = master..xp_cmdshell @SQL, no_output
IF (@result = 1)
raiserror('Error exporting results to file',11,16)
And here is the actuly working query
begin
DECLARE@counterTINYINT
DECLARE@db_nameVARCHAR(50)-- used in while loop to set database name
SETNOCOUNT ON
SET@counter = 1
select 'print getdate()'
SELECT
IDENTITY(INT, 1, 1) AS id_col,
name AS database_name
INTO#dbs
FROM
sys.databases
where state_desc = 'ONLINE'
and database_id > 4
WHILE @counter <=(SELECT MAX(id_col) FROM #dbs)
BEGIN
SET @db_name = (SELECT database_name FROM #dbs WHERE id_col = @counter)
SET @counter= @counter + 1
EXEC ('SELECT '' USE ' + @db_name + ' IF NOT EXISTS (SELECT * FROM sys.database_principals dp WHERE name = '''''' + b.name + '''''')
CREATE USER ['' + b.name + ''] FOR LOGIN ['' + s.loginname + '']
EXEC sp_addrolemember N'''''' + a.name + '''''', N'''''' + b.name + ''''''''
FROM
' + @db_name + '.sys.database_role_members m
JOIN
' + @db_name + '.sys.database_principals a
ONm.role_principal_id = a.principal_id
JOIN
' + @db_name + '.sys.database_principals b
ONm.member_principal_id = b.principal_id
JOIN
master.sys.syslogins s
ONb.sid = s.sid
WHERE b.name <> ''dbo''')
END
DROP TABLE #dbs
End
February 16, 2011 at 10:10 pm
Database permissions live in the database. So when you restore the database, the permissions still exist.
However for SQL accounts, the SID may be different on different machines. To fix it, fix the SIDs and the permissions would work and you will not have to script out the database permissions. Check "sp_change_users_login" in BOL.
February 16, 2011 at 10:14 pm
AH forgot to mention, I'm restoring the same database but from a different server with different database permissions. It's actually a refresh from production to non-prod
February 16, 2011 at 10:45 pm
Can I please suggest a slightly different procedure?
One reason is that your query will not work, as there are collation issues.
Please let me know if this works for you?
create table ##waer (col1 nvarchar (4000))
declare @sql nvarchar(4000), @sql1 nvarchar(4000)
select @sql = '
DECLARE @counter TINYINT
DECLARE @db_name VARCHAR(50) -- used in while loop to set database name
SET NOCOUNT ON
SET @counter = 1
select ''print getdate()''
SELECT
IDENTITY(INT, 1, 1) AS id_col,
name AS database_name
INTO #dbs
FROM
sys.databases
where state_desc = ''ONLINE''
and database_id > 4
WHILE @counter <= (SELECT MAX(id_col) FROM #dbs)
BEGIN
SET @db_name = (SELECT database_name FROM #dbs WHERE id_col = @counter)
SET @counter = @counter + 1
EXEC (''SELECT '''' USE '' + @db_name + '' IF NOT EXISTS (SELECT * FROM sys.database_principals dp WHERE name = '''''''''''' + b.name + '''''''''''')
CREATE USER ['''' + b.name collate database_default + ''''] FOR LOGIN ['''' + s.loginname collate database_default + '''']
EXEC sp_addrolemember N'''''''''''' + a.name collate database_default + '''''''''''', N'''''''''''' + b.name collate database_default+ ''''''''''''''''
FROM
'' + @db_name + ''.sys.database_role_members m
JOIN
'' + @db_name + ''.sys.database_principals a
ON m.role_principal_id = a.principal_id
JOIN
'' + @db_name + ''.sys.database_principals b
ON m.member_principal_id = b.principal_id
JOIN
master.sys.syslogins s
ON b.sid = s.sid
WHERE b.name <> ''''dbo'''''')
END
DROP TABLE #dbs'
insert ##waer
exec sp_executesql @sql
select @sql1 = 'exec master..xp_cmdshell ''bcp ##waer out \\auvimel000s150\Scripts\Logins.txt -c -T'', no_output'
exec sp_executesql @sql1
drop table ##waer
Please change the BCP output file location and make sure SQL has access to write to that file.
February 17, 2011 at 7:34 pm
Hey
Ah you used a global temp table and exported out.
This works fine, thanks for the help, much appreciated
Regards
Matt
February 17, 2011 at 8:21 pm
Great. Glad to be of help 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply