March 19, 2008 at 10:33 am
Does anyone have a script that lists all permissions granted to a database role? I would prefer something making use of the new SQL 2005 system views/functions, if possible.
Thanks!
Marios
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 19, 2008 at 10:55 am
Marios
I don't have time to do it now, but it should be fairly easy to get what you want by joining sys.database_permissions to sys.database_principals.
John
March 19, 2008 at 10:57 am
John Mitchell (3/19/2008)
MariosI don't have time to do it now, but it should be fairly easy to get what you want by joining sys.database_permissions to sys.database_principals.
John
Perfect, thank you, this is the starting point I needed.
Cheers! 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 19, 2008 at 11:10 am
What about using SQL 2000 tools to do the same thing? What system tables should I use?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 19, 2008 at 11:13 am
Marios
Probably best to use the system stored procedure sp_helprotect.
John
March 19, 2008 at 12:28 pm
I ran the following and got what I wanted (I think, need to look more closely):
USE dbname;
EXEC sp_helprotect null, 'rolename';
Thanks!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 2, 2008 at 6:36 am
Try running sp below:-
USE [master]
GO
/****** Object: StoredProcedure [dbo].[spRoleMembers] Script Date: 04/02/2008 13:36:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spRoleMembers]
AS
/************************************************************
Creation Date: 04/28/02 Created By: Randy Dyess
Web Site: http://www.TransactSQL.Com
Email: RandyDyess@TransactSQL.Com
Purpose: Loops through all databases and obtains member
for database roles as well as server role members.
Location: master database
Output Parameters: None
Return Status: None
Called By: None
Calls: None
Data Modifications: None
Updates:
None
************************************************************/
SET NOCOUNT ON
--Variables
DECLARE @lngCounter INTEGER
DECLARE @strDBName VARCHAR(50)
DECLARE @strSQL NVARCHAR(4000)
--Temp table to hold database and user-define role user names
CREATE TABLE #tRolemember
(
strServerName VARCHAR(50) DEFAULT @@SERVERNAME
,strDBName VARCHAR(Max)
,strRoleName VARCHAR(Max)
,strUserName VARCHAR(Max)
,strUserID VARCHAR(Max)
)
--Temp table to hold database names
CREATE TABLE #tDBNames
(lngID INTEGER IDENTITY(1,1)
,strDBName VARCHAR(50)
)
--Create permanent table
--IF OBJECT_ID ('dbo.tRolemember') IS NULL
--BEGIN
CREATE TABLE #TemptRolemember
(
strServerName VARCHAR(50)
,strDBName VARCHAR(Max)
,strRoleName VARCHAR(Max)
,strUserName VARCHAR(Max)
,strUserID VARCHAR(Max)
)
--END
--Obtain members of each server role
INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC dbo.sp_helpsrvrolemember
--Obtain database names
INSERT INTO #tDBNames (strDBName)
SELECT name FROM master.dbo.sysdatabases where name <> 'ers_mirror'
SET @lngCounter = @@ROWCOUNT
--Loop through databases to obtain members of database roles and user-defined roles
WHILE @lngCounter > 0
BEGIN
--Get database name from temp table
SET @strDBName = (SELECT strDBName FROM #tDBNames WHERE lngID = @lngCounter)
--Obtain members of each database and user-defined role
SET @strSQL = 'INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC ' + @strDBName + '.dbo.sp_helprolemember'
EXEC sp_executesql @strSQL
--Update database name in temp table
UPDATE #tRolemember
SET strDBName = @strDBName
WHERE strDBName IS NULL
SET @lngCounter = @lngCounter - 1
END
--Place data into permanent table
INSERT INTO #TemptRolemember
SELECT trm.* FROM #tRolemember trm
LEFT JOIN #TemptRolemember prm
ON trm.strUserName = prm.strUserName
AND trm.strDBName = prm.strDBName
AND trm.strRoleName = prm.strRoleName
AND trm.strServerName = prm.strServerName
WHERE prm.strServerName IS NULL
--GO
--Test Stored Procedure
--EXEC dbo.spRoleMembers
PRINT 'Display by User'
SELECT strUserName, strDBName, strRoleName, strServerName FROM #TemptRolemember
WHERE strUserName <> 'dbo'
ORDER BY strUserName
PRINT 'Display by Role'
SELECT strRoleName, strDBName, strUserName,strServerName FROM #TemptRolemember
WHERE strUserName <> 'dbo'
ORDER BY strRoleName
PRINT 'Display by Database'
SELECT strDBName, strRoleName,strUserName, strServerName FROM #TemptRolemember
WHERE strUserName <> 'dbo'
ORDER BY strDBName
April 2, 2008 at 7:04 am
This script obtains role member information, but not permissions of a role.
Try these for 2005:
SQL Server: Listing all the database roles for a given database user
SQL Server: Showing permissions for a given database user
K. Brian Kelley
@kbriankelley
April 2, 2008 at 7:13 am
see if this can help u
/*
SCRIPT TO FIND THE OBJECT LEVEL PERMISSIONS PROVIDED TO SPECIFIC LOGINS.
** THIS DOES NOT INCLUDE THE "ROLE BASED" PERMISSIONS SUCH AS PROVIDING "db_datareader" ROLE FOR A SOPECIFIC LOGIN
** THE GRANTEDBY VALUE WILL ALWAYS RETAIN "dbo" IF THE PERMISSIONS WERE GRANTED BY SOMEONE WHO HAS A "sysadmin" ROLE
** TO BE USED WITH SQL SERVER 2005
*/
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
SELECT dpm.class_desc as [AccessLevel],
ISNULL(OBJECT_NAME(dpm.major_id), 'N/A')
as [ObjectName], dpr1.name AS [GrantedTo], dpr2.name AS [GrantedBy],
CASE dpm.type
WHEN 'AL' THEN 'ALTER'
WHEN 'ALAK' THEN 'ALTER ANY ASYMMETRIC KEY'
WHEN 'ALAR' THEN 'ALTER ANY APPLICATION ROLE'
WHEN 'ALAS' THEN 'ALTER ANY ASSEMBLY'
WHEN 'ALCF' THEN 'ALTER ANY CERTIFICATE'
WHEN 'ALDS' THEN 'ALTER ANY DATASPACE'
WHEN 'ALED' THEN 'ALTER ANY DATABASE EVENT NOTIFICATION'
WHEN 'ALFT' THEN 'ALTER ANY FULLTEXT CATALOG'
WHEN 'ALMT' THEN 'ALTER ANY MESSAGE TYPE'
WHEN 'ALRL' THEN 'ALTER ANY ROLE'
WHEN 'ALRT' THEN 'ALTER ANY ROUTE'
WHEN 'ALSB' THEN 'ALTER ANY REMOTE SERVICE BINDING'
WHEN 'ALSC' THEN 'ALTER ANY REMOTE SERVICE BINDING'
WHEN 'ALSC' THEN 'ALTER ANY CONTRACT'
WHEN 'ALSK' THEN 'ALTER ANY SYMMETRIC KEY'
WHEN 'ALSM' THEN 'ALTER ANY SCHEMA'
WHEN 'ALSV' THEN 'ALTER ANY SERVICE'
WHEN 'ALTG' THEN 'ALTER ANY DATABASE DDL TRIGGER'
WHEN 'ALUS' THEN 'ALTER ANY USER'
WHEN 'AUTH' THEN 'AUTHENTICATE'
WHEN 'BADB' THEN 'BACKUP DATABASE'
WHEN 'BALO' THEN 'BACKUP LOG'
WHEN 'CL' THEN 'CONTROL'
WHEN 'CO' THEN 'CONNECT'
WHEN 'CORP' THEN 'CONNECT REPLICATION'
WHEN 'CP' THEN 'CHECKPOINT'
WHEN 'CRAG' THEN 'CREATE AGGREGATE'
WHEN 'CRAK' THEN 'CREATE ASYMMETRIC KEY'
WHEN 'CRAS' THEN 'CREATE ASSEMBLY'
WHEN 'CRCF' THEN 'CREATE CERTIFICATE'
WHEN 'CRDB' THEN 'CREATE DATABASE'
WHEN 'CRDF' THEN 'CREATE DEFAULT'
WHEN 'CRED' THEN 'CREATE DATABASE DDL EVENT NOTIFICATION'
WHEN 'CRFN' THEN 'CREATE FUNCTION'
WHEN 'CRFT' THEN 'CREATE FULLTEXT CATALOG'
WHEN 'CRMT' THEN 'CREATE MESSAGE TYPE'
WHEN 'CRPR' THEN 'CREATE PROCEDURE'
WHEN 'CRQU' THEN 'CREATE QUEUE'
WHEN 'CRRL' THEN 'CREATE ROLE'
WHEN 'CRRT' THEN 'CREATE ROUTE'
WHEN 'CRRU' THEN 'CREATE RULE'
WHEN 'CRSB' THEN 'CREATE REMOTE SERVICE BINDING'
WHEN 'CRSC' THEN 'CREATE CONTRACT'
WHEN 'CRSK' THEN 'CREATE SYMMETRIC KEY'
WHEN 'CRSM' THEN 'CREATE SCHEMA'
WHEN 'CRSN' THEN 'CREATE SYNONYM'
WHEN 'CRSV' THEN 'CREATE SERVICE'
WHEN 'CRTB' THEN 'CREATE TABLE'
WHEN 'CRTY' THEN 'CREATE TYPE'
WHEN 'CRVW' THEN 'CREATE VIEW'
WHEN 'CRXS' THEN 'CREATE XML SCHEMA COLLECTION'
WHEN 'DL' THEN 'DELETE'
WHEN 'EX' THEN 'EXECUTE'
WHEN 'IM' THEN 'IMPERSONATE'
WHEN 'IN' THEN 'INSERT'
WHEN 'RC' THEN 'RECEIVE'
WHEN 'RF' THEN 'REFERENCES'
WHEN 'SL' THEN 'SELECT'
WHEN 'SN' THEN 'SEND'
WHEN 'SPLN' THEN 'SHOWPLAN'
WHEN 'SUQN' THEN 'SUBSCRIBE QUERY NOTIFICATIONS'
WHEN 'TO' THEN 'TAKE OWNERSHIP'
WHEN 'UP' THEN 'UPDATE'
WHEN 'VW' THEN 'VIEW DEFINITION'
WHEN 'VWDS' THEN 'VIEW DATABASE STATE'
END
FROM sys.database_permissions dpm INNER JOIN sys.database_principals dpr1
ON dpm.grantee_principal_id = dpr1.principal_id INNER JOIN sys.database_principals dpr2
ON dpm.grantor_principal_id = dpr2.principal_id
WHERE dpr1.principal_id NOT IN (0,1,2,3,4,16384,16385,16386,16387,16388,16389,16390,16391,16392,16393)
April 7, 2008 at 9:12 am
This can be done without any scripting as well.
Scriptlogic is going to release a solution for this.
It looks like a new version of their enterprise security reporter 3.6 supports reporting on sql server permissions.
I had a chance to try a beta version from http://www.scriptlogic.com/beta
It looks very promising!
It includes very powerful reporting abilities like predefined customizable reports with reporting on database object permissions, database schema permissions, effective database object permissions, persmissions for selected database objects, database users and role membership, server logins and server role membership.
April 7, 2008 at 12:43 pm
Let me preface this by saying I intend no offense.
Tools are great and I'm all for them. However, having the script in your back pocket (or knowing how to build the script) is definitely an asset, too. Case in point, if you're trying to do some pro bono work for a non-profit who can't afford such a tool. 🙂
K. Brian Kelley
@kbriankelley
April 7, 2008 at 7:24 pm
K. Brian Kelley (4/7/2008)
Let me preface this by saying I intend no offense.Tools are great and I'm all for them. However, having the script in your back pocket (or knowing how to build the script) is definitely an asset, too. Case in point, if you're trying to do some pro bono work for a non-profit who can't afford such a tool. 🙂
I'd second that.
Nothing against 3rd party tools, and power to those who can afford them, but, even cost aside, I'm uncomfortable using someone else's tool, unless I can see inside it to find out exactly how it works.
Half the fun is in building the tools anyway... 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 7, 2008 at 7:32 pm
It's a fairly easy select to be run, no need for sps;
use yourdb
go
select dp.NAME AS principal_name,dp.type_desc AS principal_type_desc,
o.NAME AS object_name,p.permission_name,p.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o
on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
where dp.NAME='yourdbrolename'
April 7, 2008 at 9:28 pm
michaela (4/7/2008)
It's a fairly easy select to be run, no need for sps;use yourdb
go
select dp.NAME AS principal_name,dp.type_desc AS principal_type_desc,
o.NAME AS object_name,p.permission_name,p.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o
on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
where dp.NAME='yourdbrolename'
Unfortunately, this doesn't handle permissions against schema nor does it handle cases where there are objects of the same name in different schema. The former is the more important as a best practice is to assign permissions such as EXECUTE against the schema and not at the database level.
K. Brian Kelley
@kbriankelley
April 15, 2008 at 12:30 pm
I'm getting the following error when I run:
USE dbName;
EXEC sp_helprotect NULL, 'dbRole';
Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report.
I take it, this is because that db role has no permissions associated with it...
How can I account for this in my code?
I don't want my code to crash, but handle this gracefully.
Is there a test I can run like the following before invoking sp_helprotect?
IF EXISTS (SELECT * FROM ...)
...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply