User permissions
-
How can I get list of statement permissions like create table, create view, etc; granted to all users using TSQL. I know I can get it using Enterprise manager for every user separately, but I am lookig for a script since I have to run it on all my SQL Servers.
Thank you.
-
Permissions are stored in the sysprotects system table in each database. The Action column defines what type of permission (SELECT/UPDATE/CREATE TABLE/etc) is being set. Read SQL BOL for more details about the columns, but basically you should be able to select from that table with a CASE statement to see what permissions are set.
CREATE TABLE, CREATE VIEW, and other database wide permissions (but not permissions on the database itself) are also in the sysprotects table, but with a ID of 0
Julian Kuiters
juliankuiters.id.au -
These SP's will create scripts that create roles, allocate permissions the add users to roles.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'spScriptRolePermissions' AND type = 'P')
BEGIN
DROP PROCEDURE spScriptRolePermissions
END
GO
PRINT 'compiling spScriptRolePermissions'
GO
--***********************************************************************************
-- Stored Procedure to create script for permission allocation and role creation
--***********************************************************************************
CREATE PROCEDURE spScriptRolePermissions
@CreateRoles int = 0 -- CREATES ROLES
AS
DECLARE
@errStatement varchar(1000),
@msgStatement varchar(1000),
@DatabaseRoleID smallint,
@IsApplicationRole bit,
@ObjectID int,
@ObjectName sysname,
@DatabaseRoleName sysname,
@Owner varchar(255),
@Object varchar(255),
@Grantee varchar(255),
@Grantor varchar(255),
@ProtectType varchar(255),
@Action varchar(255),
@Column varchar(255)
SET NOCOUNT ON
Create Table #RolePermission (RolePermId smallint IDENTITY(1,1),
Owner varchar(10),
Object varchar(50),
Grantee varchar(255),
Grantor varchar(50),
ProtectType varchar(5),
[Action] varchar(10),
[Column] varchar(255))
INSERT #RolePermission (Owner, Object, Grantee, Grantor, ProtectType, [Action], [Column])
exec sp_helprotect
DECLARE csrROLENAME CURSOR
FOR
SELECT name from sysusers WHERE issqlrole = 1
AND uid = gid AND uid != 0
OPEN csrROLENAME
FETCH NEXT FROM csrROLENAME INTO @DatabaseRoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@DatabaseRoleID = uid,
@IsApplicationRole = CAST(isapprole AS bit)
FROM sysusers
WHERE
name = @DatabaseRoleName
AND
(
issqlrole = 1
OR isapprole = 1
 
AND name NOT IN
(
'public',
'INFORMATION_SCHEMA',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter'
 
IF @DatabaseRoleID IS NULL
BEGIN
IF @DatabaseRoleName IN
(
'public',
'INFORMATION_SCHEMA',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter'
 
SET @errStatement = 'Role ' + ISNULL(@DatabaseRoleName, '') + ' is a fixed database role and cannot be scripted.'
ELSE
SET @errStatement = 'Role ' + ISNULL(@DatabaseRoleName, '') + ' does not exist in ' + ISNULL(DB_NAME(), '') + '.' + CHAR(13) +
'Please provide the name of a current role in ' + ISNULL(DB_NAME(), '') + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for role ' + ISNULL(@DatabaseRoleName, '') + CHAR(13) +
'--Created At: ' + ISNULL(CONVERT(varchar, GETDATE(), 112) + ISNULL(REPLACE(CONVERT(varchar, GETDATE(), 108), ':', ''), ''), '') + CHAR(13) +
'--Created By: ' + ISNULL(SUSER_NAME(), '') + CHAR(13) +
'--Add Role To Database' + CHAR(13) + 'GO' + CHAR(13)
IF @IsApplicationRole = 1 AND @CreateRoles = 1
BEGIN
SET @msgStatement = @msgStatement + 'EXEC sp_addapprole' + CHAR(13) +
CHAR(9) + '@rolename = ''' + ISNULL(@DatabaseRoleName, '') + '''' + CHAR(13) +
CHAR(9) + '@password = ''{Please provide the password here}''' + CHAR(13) + 'GO' + CHAR(13)
END
IF @IsApplicationRole = 0 AND @CreateRoles = 1
BEGIN
SET @msgStatement = @msgStatement + 'EXEC sp_addrole' + CHAR(13) +
CHAR(9) + '@rolename = ''' + ISNULL(@DatabaseRoleName, '') + '''' + CHAR(13) + 'GO' + CHAR(13)
END
SELECT @msgStatement = @msgStatement + '--Set Object Specific Permissions For Role ' + ISNULL(@DatabaseRoleName, '')
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT Owner, Object, Grantee, Grantor, ProtectType, [Action], [Column]
FROM #RolePermission WHERE Grantee = @DatabaseRoleName
-- SELECT
-- DISTINCT(sysobjects.id),
-- USER_NAME(sysobjects.uid) + '.' + sysobjects.name
-- FROM dbo.sysprotects
-- INNER JOIN dbo.sysobjects
-- ON sysprotects.id = sysobjects.id
-- WHERE sysprotects.uid = @DatabaseRoleID
OPEN _sysobjects
FETCH NEXT FROM _sysobjects INTO @Owner, @Object, @Grantee, @Grantor, @ProtectType, @Action, @Column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF @Column = '.' OR @Column = '(All+New)' OR @Column = '(All)'
BEGIN
SET @msgStatement = ISNULL(@ProtectType, '') + CHAR(13) + CHAR(9) + ISNULL(@Action, '')
+ CHAR(13) + CHAR(9) + 'ON ' + ISNULL(@Object, '') +
CHAR(13) + CHAR(9) + 'TO [' + ISNULL(@DatabaseRoleName, '') + ']'
+ CHAR(13) + 'GO'
PRINT @msgStatement
END
ELSE
BEGIN
SET @msgStatement = ISNULL(@ProtectType, '') + CHAR(13) + CHAR(9) + ISNULL(@Action, '')
+ CHAR(13) + CHAR(9) + '(' + ISNULL(@Column, '') + ')'
+ CHAR(13) + CHAR(9) + 'ON ' + ISNULL(@Object, '') +
CHAR(13) + CHAR(9) + 'TO [' + ISNULL(@DatabaseRoleName, '') + ']'
+ CHAR(13) + 'GO'
PRINT @msgStatement
END
FETCH NEXT FROM _sysobjects INTO @Owner, @Object, @Grantee, @Grantor, @ProtectType, @Action, @Column
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
END
FETCH NEXT FROM csrROLENAME INTO @DatabaseRoleName
END
CLOSE csrROLENAME
DEALLOCATE csrROLENAME
DROP TABLE #RolePermission
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'spScriptRolePermissions' AND type = 'P')
BEGIN
PRINT 'compiled spScriptRolePermissions'
END
ELSE
BEGIN
PRINT 'compiling spScriptRolePermissions -- ERROR'
END
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'spScriptAttachUsersRoles' AND type = 'P')
BEGIN
DROP PROCEDURE spScriptAttachUsersRoles
END
GO
PRINT 'compiling spScriptAttachUsersRoles'
GO
--***********************************************************************************
-- Stored Procedure to create script for USER allocation to ROLES
--***********************************************************************************
CREATE PROCEDURE spScriptAttachUsersRoles
AS
SET NOCOUNT ON
DECLARE @errStatement varchar(8000),
@msgStatement varchar(8000),
@DatabaseUserID smallint,
@ServerUserName sysname,
@RoleName varchar(8000),
@ObjectID int,
@ObjectName varchar(261),
@DatabaseUserName sysname
DECLARE csrUSERNAME CURSOR
FOR
SELECT name from sysusers WHERE issqlrole != 1
AND issqluser != 1
OPEN csrUSERNAME
FETCH NEXT FROM csrUSERNAME INTO @DatabaseUserName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@DatabaseUserID = sysusers.uid,
@ServerUserName = master.dbo.syslogins.loginname
FROM dbo.sysusers
INNER JOIN master.dbo.syslogins
ON sysusers.sid = master.dbo.syslogins.sid
WHERE sysusers.name = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + ISNULL(@DatabaseUserName, '') + ' does not exist in ' + ISNULL(DB_NAME(), '') + CHAR(13) +
'Please provide the name of a current user in ' + ISNULL(DB_NAME(), '') + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + ISNULL(@ServerUserName, '') + CHAR(13) +
'--Created At: ' + ISNULL(CONVERT(varchar, GETDATE(), 112), '') + ISNULL(REPLACE(CONVERT(varchar, GETDATE(), 108), ':', ''), '')
+ CHAR(13) + '--Created By: ' + ISNULL(SUSER_NAME(), '') + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE ' + ISNULL(DB_NAME(), '') + '' + CHAR(13) +
'EXEC sp_grantdbaccess' + CHAR(13) +
CHAR(9) + '@loginame = ''' + ISNULL(@ServerUserName, '') + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = ''' + ISNULL(@DatabaseUserName, '') + '''' + CHAR(13) +
'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT name
FROM dbo.sysusers
WHERE
uid IN (SELECT groupuid FROM dbo.sysmembers WHERE memberuid = @DatabaseUserID)
OPEN _sysusers
FETCH NEXT FROM _sysusers INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC sp_addrolemember' + CHAR(13) +
CHAR(9) + '@rolename = ''' + ISNULL(@RoleName, '') + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + ISNULL(@DatabaseUserName, '') + ''''
PRINT @msgStatement
PRINT 'GO'
FETCH NEXT FROM _sysusers INTO @RoleName
END
CLOSE _sysusers
DEALLOCATE _sysusers
DECLARE _sysobjects CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT DISTINCT(sysobjects.id),
USER_NAME(sysobjects.uid) + '.' + sysobjects.name
FROM dbo.sysprotects
INNER JOIN dbo.sysobjects
ON sysprotects.id = sysobjects.id
WHERE sysprotects.uid = @DatabaseUserID
OPEN _sysobjects
FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '--Set Object Specific Permissions'
SET @msgStatement = ''
IF EXISTS(SELECT * FROM dbo.sysprotects WHERE id = @ObjectID AND uid = @DatabaseUserID AND action = 193 AND protecttype = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM dbo.sysprotects WHERE id = @ObjectID AND uid = @DatabaseUserID AND action = 195 AND protecttype = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM dbo.sysprotects WHERE id = @ObjectID AND uid = @DatabaseUserID AND action = 197 AND protecttype = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM dbo.sysprotects WHERE id = @ObjectID AND uid = @DatabaseUserID AND action = 196 AND protecttype = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM dbo.sysprotects WHERE id = @ObjectID AND uid = @DatabaseUserID AND action = 224 AND protecttype = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM dbo.sysprotects WHERE id = @ObjectID AND uid = @DatabaseUserID AND action = 26 AND protecttype = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + ISNULL(@ObjectName, '') + CHAR(13) +
CHAR(9) + 'TO ' + ISNULL(@DatabaseUserName, '')
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM dbo.sysprotects WHERE id = @ObjectID AND uid = @DatabaseUserID AND action = 193 AND protecttype = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM dbo.sysprotects WHERE id = @ObjectID AND uid = @DatabaseUserID AND action = 195 AND protecttype = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM dbo.sysprotects WHERE id = @ObjectID AND uid = @DatabaseUserID AND action = 197 AND protecttype = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM dbo.sysprotects WHERE id = @ObjectID AND uid = @DatabaseUserID AND action = 196 AND protecttype = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM dbo.sysprotects WHERE id = @ObjectID AND uid = @DatabaseUserID AND action = 224 AND protecttype = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM dbo.sysprotects WHERE id = @ObjectID AND uid = @DatabaseUserID AND action = 26 AND protecttype = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + ISNULL(@ObjectName, '') + CHAR(13) +
CHAR(9) + 'TO ' + ISNULL(@DatabaseUserName, '')
PRINT @msgStatement
END
FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName
PRINT 'GO'
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
END
FETCH NEXT FROM csrUSERNAME INTO @DatabaseUserName
END
CLOSE csrUSERNAME
DEALLOCATE csrUSERNAME
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'spScriptAttachUsersRoles' AND type = 'P')
BEGIN
PRINT 'compiled spScriptAttachUsersRoles'
END
ELSE
BEGIN
PRINT 'compiling spScriptAttachUsersRoles -- ERROR'
END
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply