November 23, 2010 at 12:02 am
Comments posted to this topic are about the item Script DB Level Permissions
November 23, 2010 at 6:50 am
I like the script, it will be useful.
I have added the following to your code:
DECLARE
@sql VARCHAR(2048)
,@sort INT
DECLARE tmp CURSOR FOR
then your code goes here.
OPEN tmp
FETCH NEXT FROM tmp INTO @sql, @sort
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sql
FETCH NEXT FROM tmp INTO @sql, @sort
END
CLOSE tmp
DEALLOCATE tmp
The result is now text that can be copied into another query window and run or saved to a file.
Thanks for sharing your code.
Bill
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
November 23, 2010 at 7:54 am
Thanks for the feedback. That's a good update to it so that it sorts without displaying the sort counter. I knew it could benefit from some updates.
Thanks!
Steve
November 24, 2010 at 9:06 am
Currently it captures stored procedure execute permissions, what needs modified to capture schema execute permissions?
Thank You
November 24, 2010 at 9:45 am
I believe this would get you the schema permissions you are looking for. I'll have to update my script to include that as well.
select state_desc+ ' ' + permission_name + ' TO ' + class_desc + '::' + QUOTENAME(SCHEMA_NAME(grantee_principal_id)), perm.state
from sys.database_permissions AS perm
inner join sys.schemas s
on perm.grantee_principal_id = s.schema_i
WHERE class_desc = 'SCHEMA' --class = 3
I did some minimal testing with that code and it seems to work as you desire.
I'm sure there will be other facets missing, so please let me know if you see anything else missing that would be a benefit to the community.
Thanks,
Steve
November 30, 2010 at 9:44 pm
Thanks for the Script. It is very useful. While scripting out the object level permissions, i think, the below line that has been highlighted (Underlined) should have been "QUOTENAME(SCHEMA_NAME(obj.schema_id))". Please correct me if i m wrong
/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
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) --select, execute, etc on specific objects
+ CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm
November 30, 2010 at 9:49 pm
karthik.catchme (11/30/2010)
Thanks for the Script. It is very useful. While scripting out the object level permissions, i think, the below line that has been highlighted (Underlined) should have been "QUOTENAME(SCHEMA_NAME(obj.schema_id))". Please correct me if i m wrong/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
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) --select, execute, etc on specific objects
+ CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm
Good catch, not sure how I missed that.
I'll get that fixed and will submit a v2 of this script that includes this fix as well as the enhancement to have it "PRINT" the command output instead of in a two-column grid, and will also include schema level DB permissions per the request of a previous poster.
Thanks for checking it out.
Steve
February 2, 2011 at 8:50 am
Thanks for the new v2 version. It looks great! This will be very helpful.
May 5, 2011 at 1:53 am
Are there any version to run it in SQL Server 2000? Or what needs to be changed to be able to run in SQL Server 2000?
May 5, 2011 at 4:18 pm
Unfortunately, no. I never made one for 2000 and we have so few DB's still in 2000 that it isn't worthwhile to me. There may be someone else on the thread who might have something like that.
Sorry,
Steve
May 6, 2011 at 7:32 am
Here is the code to list/view/report ALL objects permissions in SQL SERVER 2000. Code will extract DB_name, User Name, Object Name, Object Type, Action and Protect Type.
use master
go
Create View VUserRights
as
SELECT top 100 percent
U.[Name] as UserName
,O.Name as ObjectName
,case xtype
when 'S' then 'System Table'
when 'P' then 'Stored Procedure'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'L' then 'Log'
when 'FN' then 'Scalar Function'
when 'IF' then 'Inlined Table-Function'
when 'PK' then 'PRIMARY KEY'
when 'RF' then 'Replication Filter Stored Procedure'
when 'S' then 'System Table'
when 'TF' then 'Table Function'
when 'TR' then 'Trigger'
when 'U' then 'User Table'
when 'UQ' then 'UNIQUE Constraint'
when 'V' then 'View'
when 'X' then 'Extended Stored Procedure'
else cast(xtype as varchar(30))
end as XType
,Case p.[action]
When 26 then 'REFERENCES'
When 178 then 'CREATE FUNCTION'
When 193 then 'SELECT'
When 195 then 'INSERT'
When 196 then 'DELETE'
When 197 then 'UPDATE'
When 198 then 'CREATE TABLE'
When 203 then 'CREATE DATABASE'
When 207 then 'CREATE VIEW'
When 222 then 'CREATE PROCEDURE'
When 224 then 'EXECUTE'
When 228 then 'BACKUP DATABASE'
When 233 then 'CREATE DEFAULT'
When 235 then 'BACKUP LOG'
When 236 then 'CREATE RULE'
Else cast([Action] as varchar(20))
End as 'Action'
,Case p.protecttype
When 204 Then 'GRANT_W_GRANT'
When 205 Then 'GRANT'
When 206 Then 'REVOKE'
Else cast(protecttype as varchar(20))
end as ProtectType
FROM sysusers U join sysprotects P
on u.uid = P.uid
Join sysobjects O
on P.id = O.id
where xtype <>'s'
order by U.uid ASC, O.xtype Desc
--drop view VUserRights
/*
Here are some calling statements
--2 is an oracle trick that i learned to
create a permissions assignment statement from exisiting metadata
*/
GO
--1
--select * from vuserrights
--Go
--2
-- select Protecttype + ' ' + Action + ' ON ' + Oname
-- +'('+ Xtype+')'
-- + ' TO ' + Uname from vuserRights
-- Reply With Quote
--select * from sysobjects where name like 'ALS_AppLetterRefNos%'
--select * from sysdatabases where name like 'ALS%'
--select * from vuserrights
CREATE TABLE #t(d SYSNAME, t SYSNAME);
EXEC sp_msForEachDB 'INSERT #t SELECT ''?'', TABLE_NAME
FROM [?].INFORMATION_SCHEMA.TABLES
--WHERE TABLE_TYPE=''BASE TABLE''
;';
select #t.D DBname, V.* from #t right join vuserrights V on #t.t=V.Objectname
--select * from vuserrights
--select * from vuserrights where Oname NOT EXISTS(select #t.D, #t.t, V.* from #t right join vuserrights V on #t.t=V.Oname)
--SELECT * FROM #t ORDER BY d,t;
--select * from INFORMATION_SCHEMA.TABLES
DROP TABLE #t;
May 14, 2012 at 9:14 am
I'm aware this is an old post, But I feel there may be a small error in the schema script section.
that section will result in an out put something like this
GRANT SELECT TO SCHEMA::[HumanResources] (The syntax may be wrong, more over it fetches the first schema and in the subsequent order- and not the reuired schema)
The script may have to modified this way
SELECTCASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
+ QUOTENAME(SCHEMA_NAME(major_id))
+ SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(grantee_principal_id) + ']' COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
inner join sys.schemas s
on perm.grantee_principal_id = s.schema_id
WHERE class = 3
Now the result returns as (For e.g)
GRANT SELECT ON SCHEMA::[Sales] TO [ag]
By the way thank you for consolidating this awesome script 🙂
-Arun
May 14, 2012 at 9:52 am
aruopna (5/14/2012)
I'm aware this is an old post, But I feel there may be a small error in the schema script section.that section will result in an out put something like this
GRANT SELECT TO SCHEMA::[HumanResources] (The syntax may be wrong, more over it fetches the first schema and in the subsequent order- and not the reuired schema)
The script may have to modified this way
SELECTCASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
+ QUOTENAME(SCHEMA_NAME(major_id))
+ SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(grantee_principal_id) + ']' COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
inner join sys.schemas s
on perm.grantee_principal_id = s.schema_id
WHERE class = 3
Now the result returns as (For e.g)
GRANT SELECT ON SCHEMA::[Sales] TO [ag]
By the way thank you for consolidating this awesome script 🙂
-Arun
Hi Arun,
Thanks for your feedback. I suppose a lot of people (including myself) do not use schema-based permissions. Do you have an example that I can establish schema level permissions and test out of the update?
I ran the following code, but I cannot get it to come back as part of the DB level schema permissions.
GRANT SELECT ON SCHEMA::[test_perms_schema] TO [test_perms_login]
Any example you can provide that I can re-test the code would certainly help. I will then get the updated script posted to include your fix. I truly appreciate it, but want to be sure this time around.
Thanks, and have a great day.
Steve
May 14, 2012 at 10:54 pm
Hello Steve,
here's an example (probably sketched with too much detail :-))
--Create a Login and a corresponding user
USE master
GO
CREATE LOGIN aG WITH PASSWORD = 'somepwd',CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
USE AdventureWorks
GO
CREATE USER aG FOR LOGIN aG
GO
--Let us try authenticating as aG and see the results
USE AdventureWorks
GO
EXECUTE AS USER = 'aG'
SELECT TOP 10 * FROM Sales.Customer
/*Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'Customer', database 'AdventureWorks', schema 'Sales'.*/
REVERT
--Grant explicit privileges
GRANT SELECT ON SCHEMA::[Sales] TO [aG]
GO
--Authenticate again using aG
EXECUTE AS USER = 'aG'
SELECT TOP 10 * FROM Sales.Customer
--You should be able to select the top 10 records. (just to make sure the permissions are granted)
--Now Run the scripts
REVERT
--Existing script
USE AdventureWorks
GO
SELECTCASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'TO' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
+ QUOTENAME(SCHEMA_NAME(grantee_principal_id))
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
inner join sys.schemas s
on perm.grantee_principal_id = s.schema_id
WHERE class = 3
-- SQL STATEMENTS -- -- RESULT ORDER HOLDER --
----------------------------------------------------------------------------
--GRANT SELECT TO SCHEMA::[HumanResources] 10
--Updated script
USE AdventureWorks
GO
SELECTCASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
+ QUOTENAME(SCHEMA_NAME(major_id))
+ SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(grantee_principal_id) + ']' COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
inner join sys.schemas s
on perm.grantee_principal_id = s.schema_id
WHERE class = 3
-- SQL STATEMENTS -- -- RESULT ORDER HOLDER --
----------------------------------------------------------------------------
--GRANT SELECT ON SCHEMA::[Sales] TO [ag]10
--You can still do the same thing using GUI.
--Navigate through Instance name > databasename > Security > Schemas > Properties > Permission tab
--Grant/revoke explict privileges. Run the scripts again.
--I have verified with 'WITH GRANT OPTION'; also for Windows authenticated login, works fine for me.
Cheers!
January 24, 2013 at 10:00 am
S. Kusen, thank you for this helpful script!
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply