Using Old User Permission to New User Role Script

set nocount on

EXEC sp_MSforeachdb 'use ? ;

declare @UserName nvarchar(30)

declare @NewUserName nvarchar(30)

SET @UserName=''VenkatMehar''   ----- OldUserName

SET @NewUserName=''Jagadeesh''  ------ NewUserName

if (SELECT COUNT(*) FROM sys.sysusers where name=@UserName)<>0

BEGIN

create table #DB_rolePermissions( UserName nvarchar(40),RoleName nvarchar(40),LoginName nvarchar(40),DefDBName nvarchar(40),DefSchemaName nvarchar(40),UserID nvarchar(40),SID nvarchar(40))

INSERT INTO #DB_rolePermissions

EXEC sp_helpuser @UserName

print ''Use ''+ DB_name() + '' ;''

PRINT ''EXEC sp_grantdbaccess [''+@NewUserName+'']''

select ''EXEC sp_addrolemember [''+ RoleName +''],''+''[''+@NewUserName+'']'' from #DB_rolePermissions

DROP TABLE #DB_rolePermissions

 

---- Object Level Permissions

create table #User (DbName varchar(20), UserName varchar(20),Permiss nvarchar(120))

insert into #User

SELECT  DB_NAME(), username            =   sys.database_principals.name

,       permissionsql       =   state_desc + '' '' + permission_name

                                 + '' on [''+ sys.schemas.name + ''].['' + sys.objects.name

                                 + ''] to ['' + @NewUserName + '']''

                                  COLLATE LATIN1_General_CI_AS

 FROM sys.database_permissions

 INNER JOIN sys.objects ON sys.database_permissions.major_id =      sys.objects.object_id

 INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id

 INNER JOIN sys.database_principals ON sys.database_permissions.grantee_principal_id =  sys.database_principals.principal_id

where sys.database_principals.name = @UserName

SELECT ''USE '' + DbName+ ''; ''+Permiss  from #User

PRINT ''grant execute to ''+@NewUserName

PRINT ''grant view definition to ''+@NewUserName

Print ''----XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 

''

drop table #User

END'

 

This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful.