August 18, 2021 at 9:43 am
Hi Everyone
I have the below script which is very useful for single database usage. (Credit to the author)
How can I make this execute against all my user databases?
Thanks
SET NOCOUNT ON
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'
select ' IF NOT EXISTS (select * from sys.database_principals where name = '''+ dp.name collate database_default +''')CREATE USER [' + dp.name collate database_default + '] FOR LOGIN [' + sp.name + ']'+
case dp.type
when 'G' then ' '
else
' WITH DEFAULT_SCHEMA=['+dp.default_schema_name + ']'
end
as '-- by default Orphaned users will not be recreated'
from sys.server_principals sp
inner join sys.database_principals dp on dp.sid = sp.sid
where dp.principal_id > 4 and dp.owning_principal_id is NULL and sp.name <> ''
order by dp.name
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'
SELECT '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
join sys.database_principals dp on rm.role_principal_id = dp.principal_id
--WHERE USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ 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 '--Object Level Permissions'
FROM sys.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 perm.permission_name ASC, perm.state_desc ASC
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
AND perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
August 18, 2021 at 11:03 am
Would be a case of wrapping it in a sp_msforeachdb or using something like this wrapper I have for per DB executions.
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
SELECT
'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
/*Put you per DB query in this segment*/
/*End of per DB query*/
FROM sys.databases WHERE STATE_DESC = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')
--select @sql
EXECUTE sp_executesql @SQL
August 18, 2021 at 11:05 am
An alternative would be to use DBATools and use their permission scripting capabilities, which do a lot more than the script you have provided.
August 18, 2021 at 1:10 pm
An alternative would be to use DBATools and use their permission scripting capabilities, which do a lot more than the script you have provided.
Thanks
I have been using DBATools for almost everything I do including this kind of thing
Was just trying to figure out a way to get that script to run against all my DB's using T-SQL. It's not that hard just very tedious working through all the single/double quotes to get it to work.
Thanks
August 18, 2021 at 3:20 pm
Hi Everyone
I have the below script which is very useful for single database usage. (Credit to the author)
How can I make this execute against all my user databases?
Thanks
SET NOCOUNT ON
SELECT'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'
select ' IF NOT EXISTS (select * from sys.database_principals where name = '''+ dp.name collate database_default +''')CREATE USER [' + dp.name collate database_default + '] FOR LOGIN [' + sp.name + ']'+
case dp.type
when 'G' then ' '
else
' WITH DEFAULT_SCHEMA=['+dp.default_schema_name + ']'
end
as '-- by default Orphaned users will not be recreated'
from sys.server_principals sp
inner join sys.database_principals dp on dp.sid = sp.sid
where dp.principal_id > 4 and dp.owning_principal_id is NULL and sp.name <> ''
order by dp.name
SELECT'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'
SELECT'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
join sys.database_principals dp on rm.role_principal_id = dp.principal_id
--WHEREUSER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC
SELECTCASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ 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 '--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 perm.permission_name ASC, perm.state_desc ASC
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
ANDperm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
My recommendation would be to NOT do this for individual users. Instead, create an Active Directory User Group and give that group the privs on SQL Server. Then, it all becomes an easy task of adding a person to that group or removing them (at the domain controller, of course).
--Jeff Moden
August 18, 2021 at 5:14 pm
Agree completely with Jeff's suggestion as the best option.
Don't forget that there is a lot you can do using registered servers in SSMS and executing a query against multiple servers. I used this to do a quick and dirty check for gathering SP level and various other useful bits and pieces across the estate.
Use with care when using it to do anything other than SELECTs though.
Edit: It's an often overlooked, but useful, feature., so apologies if I'm teaching you to suck eggs. Here's the MS documentation.
Happy for any greybeards to jump in and say why it should not be used, but I've not seen much anywhere to say it's a bad idea. Obviously no good for scripting/automation as you have to use SSMS.
Doh! just re-read your original post. I read it first as across all servers, not databases. Ignore me, but I'll leave this here in case useful to anyone else lurking.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply