script logins with user permissions and roles in SQL Server 2005


Problem

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. The most common symptoms of this problem are that the application may face login failed errors or you may get a message like the user already exists in the current database when you try to add the login to the database. This is a common scenario when performing an attach or a restore, so how do you resolve this?
 

Solution

 
When a database is moved from one server to another server the login ids stored in the master database do not align with the login ids stored in each user database. As mentioned above some of the errors you may see after attaching or restoring a database include:
Msg 229, Level 14, State 1
%s permission denied on object %.*s, database %.*s, owner %.*s

or
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role ‘%s’ already exists in the current database.

 

 
Without proper understanding and appropriate planning you may face this problem. You could delete and recreate the user, but you will lose all of the configured permissions. So a proper linking mechanism is required, so that permissions are retained. Script the permissions and rolemembers on Primary Server (Server 1) using below T-SQL queries and run the scripts  on the Secondary Server (Server 2) where the  Database Users and the Existing Logins are to be linked to get out of the issues.

Use below T-SQL statements to get the scripts of Role Permissions, Object level permissions, Database level permissions.

It worked in SQL Server 2000, 2005, 2008.

________________________________________________________________________________________

———-For Role Memberships, use the below T-SQL statements to get script.————–

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’
FROM sys.database_role_members AS rm
ORDER BY rm.role_principal_id

——— For Object Level Permissions, use the below T-SQL statements to get script. ————–

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

+

CASEWHEN cl.column_id ISNULLTHENSPACE(0)ELSE‘(‘+QUOTENAME(cl.name)+‘)’

END

+

SPACE(1)+‘TO’+SPACE(1)+QUOTENAME(usr.name

)

+

CASEWHEN perm.state!=‘W’THENSPACE(0)ELSESPACE(1)+‘WITH GRANT OPTION’ENDAS

‘–Object Level Permissions’

FROM

sys.database_permissionsAS perm

INNER

JOIN

sys

.objectsAS obj

ON

perm.major_id = obj.[object_id]

INNER

JOIN

sys

.database_principalsAS usr

ON

perm.grantee_principal_id = usr.principal_id

LEFT

JOIN

sys

.columnsAS cl

ON

cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

ORDER

BY usr.name

— ——–For Database Level Permissions, use the below T-SQL statements to get script.—————

 
SELECT CASE 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’
FROM sys.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

________________________________________________________________________________________

Have a nice scripting…..

Advertisements

9 responses

  1. I couldn’t resist commenting. Vегy well written!

  2. Hello to every one, it’s really a fastidious for me to pay
    a quick visit this web site, it includes priceless Information.

  3. It’s actually a nice and helpful piece of information. I
    am happy that you just shared this useful information with us.

    Please stay us up to date like this. Thank you for sharing.

  4. At this moment I am ready to do my breakfast, after having my breakfast coming
    over again to read further news.

  5. HI thanks buddy
    But i also got same error(i’m running this script on SQL server 2008 )

  6. I constantly emailed this web site post page to all my associates,
    since if like to read it then my contacts will too.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: