May 29, 2008 at 2:55 am
Hi,
How to script the users logins and their permissions in sql 2005
Regards,
S.balavenkatesh
May 31, 2008 at 3:14 pm
Here is how to script out the logins:
How to transfer the logins and the passwords between instances of SQL Server 2005
That doesn't grab the permissions, however, as most permissions are at the database level. Is this where you're interested in permissions or are there specific server permissions you're after?
K. Brian Kelley
@kbriankelley
December 5, 2008 at 8:34 am
I'm looking for a script that would grab the logins and associated server roles, database users and associated databases roles and their permissions. Anyone already did something like that?
___________________________________
I love you but you're standing on my foot.
December 23, 2008 at 7:49 am
Hey guys,
I think this will help http://www.sqlservercentral.com/scripts/SQL+2000/61878/
Seems to work just fine on SQL 2005
December 24, 2008 at 8:56 am
Anyone else have errors creating this sp? When I try to create the sp i get the error below. When I run the block of code where the line errors on, it can run fine by itself. I cant figure out why it bombs.
Error when creating sp ####
Msg 102, Level 15, State 1, Procedure sp_ScriptRoles, Line 86
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure sp_ScriptRoles, Line 252
Incorrect syntax near 'END'.
Line 86 #####
SELECT 'EXEC sp_addrolemember ''' + RTrim(r.name) + ''',''' + RTrim(Coalesce(l.name,u.name)) + '''' as '-- Add Role Users'
complete section for line 86#########
SELECT 'EXEC sp_addrolemember ''' + RTrim(r.name) + ''',''' + RTrim(Coalesce(l.name,u.name)) + '''' as '-- Add Role Users'
FROM dbo.sysusers u
INNER JOIN sysmembers m
ON (u.uid = m.memberuid)
INNER JOIN sysusers r
ON (m.groupuid = r.uid)
LEFT OUTER JOIN master.dbo.syslogins l
ON (u.sid = l.sid)
WHERE (r.issqlrole = 1
AND u.name <> 'dbo'
AND r.name LIKE '%')
ORDER BY r.name,u.name
Code when run by itself returns#######
-- Add Role Users
EXEC sp_addrolemember 'RSExecRole','NT AUTHORITY\NETWORK SERVICE'
EXEC sp_addrolemember 'RSExecRole','NT AUTHORITY\SYSTEM'
Any help would be appreciated!
April 9, 2009 at 5:04 pm
Yeah, there are some high-order bits in the file
They look like spaces but are not...
If you copy the code into notepad you will see ?what look like boxes instead of spaces.
Remove them and you should be good to go 🙂
April 10, 2009 at 4:01 pm
Check this out.
--Role Memberships'
SELECT --rm.role_principal_id,
'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''')
+ ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS '--Role Memberships'
FROMsys.database_role_members AS rm
ORDER BY rm.role_principal_id
--Object Level Permissions'
SELECT
CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +
perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(Schema_NAME(obj.schema_id)) + '.'
+ QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROMsys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
ORDER BY usr.name
--Database Level Permissions'
SELECTCASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROMsys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE
--usr.name = @OldUser
--AND
perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
Thanks
Prem
April 14, 2009 at 6:51 am
Sweet, exactly what I was looking for.
Thanks!
___________________________________
I love you but you're standing on my foot.
December 24, 2009 at 8:24 am
Very nice.
Thank you.
April 8, 2010 at 9:07 pm
Hi All,
Older post.... but the new requirement......
I also executed the same script and tried as Kelly & Rowshambo suggested, but it failed with the below error...
___________________________________________________________________________
Msg 102, Level 15, State 1, Procedure sp_ScriptRoles, Line 4
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Procedure sp_ScriptRoles, Line 74
Must declare the scalar variable "@Role".
Msg 102, Level 15, State 1, Procedure sp_ScriptRoles, Line 112
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure sp_ScriptRoles, Line 340
Incorrect syntax near 'END'.
__________________________________________________________________________
Can any one help me to fix this...
My need is : script logins + passwords with permissions + scripting schema for every login.
Schema: our env is like, when we create a LOGIN through application, it creates -
Login + User on db + password + permissions + schema = with same Login name.
So is there any way to get the same to be scripted and executed on the secondary server to make this equivalent to production.
Simply - Server2 to be cloned or replicated as Server1. With Logins + Permissions on DB + Schema.
My head is hammered from past 3 days, and finally thought of posting here to get any help and best suggestion..
-Win.
Cheers,
- Win.
" Have a great day "
April 9, 2010 at 12:52 am
Hi All,
I could get the Roles + Permissions + Object level permissions.
My Query : I also need a SCHEMA scripts and the same schema scripts are to be mapped to the respective Users in dbd level.
As i said , every Login has its own schema and user with same name.
Please help me how to map a user to respective schema.
-Win.
Cheers,
- Win.
" Have a great day "
June 23, 2010 at 2:56 pm
it misses schema permissions
SELECTCASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + 'ON SCHEMA ::['+sc.name +']'+ SPACE(1)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Schema Level Permissions'
FROMsys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
INNER JOIN sys.schemas sc
ON sc.principal_id=perm.major_id
WHEREusr.name = @OldUser
and class_desc='SCHEMA'
ORDER BY perm.permission_name ASC, perm.state_desc ASC
did not debug in many cases, but works so far
thanks
June 24, 2010 at 8:26 am
Idera has a free tool called SQL Permissions that does exactly what you're looking for.
We use it and it works great.
Tim White
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply