Bobs Script Vault

Sessions: 72	The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
LifeAsBob		Thursday, April 25, 2024

lifeasbob.com

Home
Blog
bob tech
Calendar
Contact Bob
Pictures
VW Buses
HomeBrew
Cook Book

Chevy Express AWD 2014

bob Links

All About Stored Pro...

'Twas a woman who drove me to drink, and I never had the courtesy to thank her for it. - W.C. Fields

Bob Code 5579

Things

Script Vault:

280 scripts loaded.

Script Name Code Description

Script

set nocount on

declare @sql_Role			as varchar(1000)
declare @sql_Object			as varchar(1000)
declare @sql_DB				as varchar(1000)

declare @everything_Role	table ([database] varchar(100), [role] varchar(100), [user] varchar(100))
declare @everything_Object	table ([database] varchar(100), [Schema] varchar(100), [user] varchar(100), [Permission_Type] varchar(100), [Permission_Desc] varchar(100), [Object_Name] varchar(100))
declare @everything_DB		table ([database] varchar(100), [Permission_Type] varchar(100), [Permission_Desc] varchar(100), [user] varchar(100))

declare @oldUser as varchar(100) = 'hrbinc\X127195'
declare @newUser as varchar(100) = 'hrbinc\X160210'

SELECT '--Role Level Permissions'

set @sql_Role = 'select 
					''?'' as [database]
					, roles.[name] as [role]
					, users.[name] as [user]
from [?].sys.database_role_members members
inner join [?].sys.database_principals as roles
  on roles.principal_id = members.role_principal_id
inner join [?].sys.database_principals as users
  on users.principal_id = members.member_principal_id
 Where users.[name] = ' + char(39) + @OldUser + char(39)

insert into @everything_Role
exec sp_MSforeachdb @sql_Role

select char(9) + '---Old Roles'

select 
				 char(9) +   '---use ['+[database]+']; EXEC SP_ADDROLEMEMBER '''+[role]+''', '''+[user]+''';' 
			from @everything_Role
			where [user] = @oldUser

select char(9) + '---New Roles'

--Set read_only databases to read_write
			Select distinct 'Alter database ' + [database] + ' Set Read_Write;'
			from sys.databases sd
			join @everything_Role er
			on sd.name = er.[database]
			where sd.is_read_only = 1

select 
			--[database],[role],@newUser as [user],
				 char(9) + 'use ['+[database]+']; EXEC SP_ADDROLEMEMBER '''+[role]+''', '''+@newUser+''';'
			from @everything_Role
			where [user]=@oldUser

Select distinct 'Alter database ' + [database] + ' Set Read_Only;'
			from sys.databases sd
			join @everything_Role er
			on sd.name = er.[database]
			where sd.is_read_only = 1

--------------------------------------------------------------------------
SELECT '--Object Level Permissions'

set @sql_Object = 'Select 
					''?'' as [database]
					,sch.[name] as [schema]
					,usr.[name] as [user]
					,CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''Grant'' END as [Permission_Type]
					,perm.permission_name as [Permission_Desc]
					, obj.[name] as [Object_Name]
						from [?].sys.database_permissions as perm
						Inner Join [?].sys.objects as obj
							on perm.major_id = obj.[object_id]
						Inner Join [?].sys.schemas as sch
							on obj.schema_id = sch.schema_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
						where usr.name = ' + char(39) + @OldUser + char(39)

insert into @everything_Object
exec sp_MSforeachdb @sql_Object

select char(9) + '---Old Object Level'
			select 
				char(9) + '---use [' + [database] + ']; ' + Permission_Type + ' ' + Permission_Desc + ' ON '+ [schema] + '.' + [Object_Name] + ' TO [' + @OldUser + ']'
			 from @everything_Object
			 where [user] = @Olduser

select char(9) + '---New Object Level'

Select distinct 'Alter database ' + [database] + ' Set Read_write;'
			from sys.databases sd
			join @everything_Object er
			on sd.name = er.[database]
			where sd.is_read_only = 1

select 
				char(9) + 'use [' + [database] + ']; ' + Permission_Type + ' ' + Permission_Desc + ' ON '+ [schema] + '.' + [Object_Name] + ' TO [' + @Newuser + ']'
			 from @everything_Object
			 where [user] = @Olduser

Select distinct 'Alter database ' + [database] + ' Set Read_only;'
			from sys.databases sd
			join @everything_Object er
			on sd.name = er.[database]
			where sd.is_read_only = 1

--------------------------------------------------------------------------------------------------------
SELECT '--DB Level Permissions'
Set @sql_DB = 'Select 
					''?'' as [database]
					,CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''Grant'' END as [Permission_Type]
					, perm.permission_name as Permission_Desc
					,usr.name as [user]
				From [?].sys.database_permissions as perm
				Inner Join [?].sys.database_principals as usr
					on perm.grantee_principal_id = usr.principal_id
				Where usr.name = ' + char(39) + @OldUser + char(39)

insert @everything_DB
exec sp_MSforeachdb @sql_DB

select 	char(9) + '---Old DB Level'
		select
			char(9)+ '--use [' + [database] + ']; ' + Permission_Type + ' ' + Permission_Desc + ' TO [' + @OldUser + ']'
		from @everything_DB
		where [user] = @OldUser

select char(9) + '---New DB Level'

Select distinct 'Alter database ' + [database] + ' Set Read_write;'
		from sys.databases sd
		join @everything_DB er
		on sd.name = er.[database]
		where sd.is_read_only = 1

select
			char(9) + 'use [' + [database] + ']; ' + Permission_Type + ' ' + Permission_Desc + ' TO [' + @Newuser + ']'
		from @everything_DB
		where [user] = @OldUser

Select distinct 'Alter database ' + [database] + ' Set Read_only;'
		from sys.databases sd
		join @everything_DB er
		on sd.name = er.[database]
		where sd.is_read_only = 1

Notes:

Update by:	a546215
Updated On:	2/2/2018 9:01:00 AM
Created by:	a546215
Created On:	2/2/2018 9:00:00 AM

Script Name:

Think ! The text boxes below only works if your particular SQL Server is available via where this website is hosted / on the internet, so this is really code that is for me !; though I am working to update it to run either server side or client side via a check box, this will be a nice future enhancement (think AJAX).
Integrated Authentication	Time Out: Wrap cell contents in results Display Messages (Print)	Run For a specific database>