August 20, 2012 at 11:16 am
I am in the process of moving a database from SQL 2005 to 2008 and it has over 1,000 userids. I know how to script out the logins with sp_help_revlogin. My question is how do I script out above the database rights like who has 'sa' rights, 'security admin' type rights. I am sure there is a script to get this out of SQL Server but I cannot seem to find it.
August 20, 2012 at 2:08 pm
This should do it
CREATE TABLE #srvroles(ServerRole VARCHAR(20), Membername VARCHAR(200), MEMBERSID VARBINARY(MAX))
GO
INSERT #srvroles EXEC sp_helpsrvrolemember
SELECT 'EXEC master..sp_addsrvrolemember @loginame = N'+''''+MemberName+''''+','+'@rolename = N'+''''+ServerRole+''''
FROM #srvroles
GO
DROP TABLE #srvroles
August 20, 2012 at 2:33 pm
JeremyE (8/20/2012)
This should do it
CREATE TABLE #srvroles(ServerRole VARCHAR(20), Membername VARCHAR(200), MEMBERSID VARBINARY(MAX))GO
INSERT #srvroles EXEC sp_helpsrvrolemember
SELECT 'EXEC master..sp_addsrvrolemember @loginame = N'+''''+MemberName+''''+','+'@rolename = N'+''''+ServerRole+''''
FROM #srvroles
GO
DROP TABLE #srvroles
That will give you Fixed Server Role memberships. You'll also need to consult the sys.server_permissions System View to gain the complete picture.
Edit: as a side note sp_addsrvrolemember was marked obsolete in SQL Server 2012 so if this code is going in your toolbox you may want to consider using ALTER SERVER ROLE instead on 2012 instances.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 21, 2012 at 6:03 am
Thanks a million gang.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply