Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Script DB Level Permissions Expand / Collapse
Author
Message
Posted Friday, May 6, 2011 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 6, 2011 7:26 AM
Points: 2, Visits: 5
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;


Post #1104568
Posted Monday, May 14, 2012 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 11:49 AM
Points: 7, Visits: 473
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

SELECT CASE
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
Post #1299638
Posted Monday, May 14, 2012 9:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 7:45 AM
Points: 968, Visits: 971
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

SELECT CASE
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
Post #1299677
Posted Monday, May 14, 2012 10:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 11:49 AM
Points: 7, Visits: 473
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
SELECT CASE
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
SELECT CASE
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!
Post #1300037
Posted Thursday, January 24, 2013 10:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 192, Visits: 409
S. Kusen, thank you for this helpful script!
Post #1411236
Posted Thursday, January 24, 2013 12:08 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 7:45 AM
Points: 968, Visits: 971
AmarettoSlim (1/24/2013)
S. Kusen, thank you for this helpful script!


Glad you got some good use out of it!

Cheers.
Post #1411295
Posted Wednesday, July 24, 2013 12:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 1:26 PM
Points: 8, Visits: 277
I had an older version of this script that I had inherited from my predecessor, stripped of any author information, and was just about to edit it so that it would script out schema permissions as well, but decided to ask google about it first. Lo and behold, I found 2.1, and saved myself a bit of work. The original script has already been more than useful, so I just wanted to say thanks!
Post #1477200
Posted Friday, January 24, 2014 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 9:25 AM
Points: 7, Visits: 55
I have to say that this script is great and it works amazing.

I have a suggestion about database permissions on SQL 2012. At our current location, we change database containment type to Partial. SQL logins are created at the database level as compared to server level. So when I script permissions out, the scripts will create SQL login without the password but cannot apply it since it cannot find the SQL login. Is there any option to add this?

Thanks!
Post #1534554
Posted Friday, January 24, 2014 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 11:49 AM
Points: 7, Visits: 473

Do you mean to say you need something like this? Try executing this piece (in text mode) on your database, it will generate create user scripts only for those users without a login.


SET NOCOUNT ON

DECLARE @name_holder VARCHAR(255)
DECLARE my_cursor CURSOR FOR
SELECT name
FROM sys.database_principals
WHERE Datalength(sid) >= 28
AND sid NOT IN (SELECT sid
FROM sys.server_principals)
AND type = 'S'
AND principal_id > 4

OPEN my_cursor

FETCH next FROM my_cursor INTO @name_holder

WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SELECT
'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '
+ Space(1) + '''' + name + '''' + ') BEGIN CREATE USER '
+ Space(1) + Quotename([name])
+ ' WITHOUT LOGIN WITH DEFAULT_SCHEMA = '
+ Quotename([default_schema_name]) + Space(1)
+ 'END; '
FROM sys.database_principals AS rm
WHERE [type] IN ( 'U', 'S', 'G' )
AND name = @name_holder

FETCH next FROM my_cursor INTO @name_holder
END

CLOSE my_cursor

DEALLOCATE my_cursor
Post #1534600
Posted Friday, January 24, 2014 1:35 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 7:45 AM
Points: 968, Visits: 971
aruopna (1/24/2014)

Do you mean to say you need something like this? Try executing this piece (in text mode) on your database, it will generate create user scripts only for those users without a login.


SET NOCOUNT ON

DECLARE @name_holder VARCHAR(255)
DECLARE my_cursor CURSOR FOR
SELECT name
FROM sys.database_principals
WHERE Datalength(sid) >= 28
AND sid NOT IN (SELECT sid
FROM sys.server_principals)
AND type = 'S'
AND principal_id > 4

OPEN my_cursor

FETCH next FROM my_cursor INTO @name_holder

WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SELECT
'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '
+ Space(1) + '''' + name + '''' + ') BEGIN CREATE USER '
+ Space(1) + Quotename([name])
+ ' WITHOUT LOGIN WITH DEFAULT_SCHEMA = '
+ Quotename([default_schema_name]) + Space(1)
+ 'END; '
FROM sys.database_principals AS rm
WHERE [type] IN ( 'U', 'S', 'G' )
AND name = @name_holder

FETCH next FROM my_cursor INTO @name_holder
END

CLOSE my_cursor

DEALLOCATE my_cursor


Thanks for this. I'll evaluate adding it to the script. I didn't adjust the script for contained DB users, obvioiusly. I'll see what I can do for getting an update out for that.


Appreciate you asking, Srizwanh, as well.

Steve
Post #1534634
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse