[推薦](T-SQL) 得到一個(gè)給定用戶使用了的權(quán)限腳本
得到一個(gè)給定用戶使用了的權(quán)限腳本，直接上代碼，自己研究。
[推薦]得到一個(gè)給定用戶使用了的權(quán)限腳本
——通過知識(shí)共享樹立個(gè)人品牌。
得到一個(gè)給定用戶使用了的權(quán)限腳本，直接上代碼，自己研究。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_user_permissions_script_get]
-----------------------------------------------------------
-- OBJECT NAME :dbo.p_user_permissions_script_get
-- AUTHOR: EricHu
-- DATE: 01/05/2012
-- INPUT PARAMETERS:
@userName VARCHAR(500)
--
-- OUTPUT PARAMETERS: none
-- DEPENDENCIES: none
-- DESCRIPTION: Used to script out permissions for a given user
-- MODIFICATION HISTORY:
-------------------------------------------------------------
AS
SET NOCOUNT ON
DECLARE @DatabaseUserName [SYSNAME];
SET @DatabaseUserName = @userName;
DECLARE @errStatement VARCHAR(1000),
@msgStatement VARCHAR(1000),
@DatabaseUserID SMALLINT,
@ServerUserName SYSNAME,
@RoleName VARCHAR(1000),
@ObjectID INT,
@ObjectName VARCHAR(1000),
@StateDesc VARCHAR(1000),
@permissionName VARCHAR(1000)
SELECT @DatabaseUserID = su.[uid],
@ServerUserName = sl.[loginname]
FROM dbo.[sysusers] su
INNER JOIN [master].dbo.[syslogins] sl
ON su.[sid] = sl.[sid]
WHERE su.[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME()
+ CHAR(13) + 'Please provide the name of a current user in ' + DB_NAME()
+ ' you wish to script.'
RAISERROR(@errStatement,
16,
1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13)
+ '--Created At: ' + CONVERT(VARCHAR, GETDATE(), 100)
+ REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '') + CHAR(13)
+ '--Created By: ' + SUSER_NAME() + CHAR(13) + '--Add User To Database'
+ CHAR(13) + 'USE [' + DB_NAME() + ']' + CHAR(13)
+ 'EXEC [sp_grantdbaccess]' + CHAR(13) + CHAR(9)
+ '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) + CHAR(9)
+ '@name_in_db = ''' + @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 = '''
+ @RoleName + ''',' + CHAR(13) + CHAR(9) + '@membername = '''
+ @DatabaseUserName + '''' + ';' ;
PRINT @msgStatement
FETCH NEXT FROM _sysusers INTO @RoleName
END
CLOSE _sysusers;
DEALLOCATE _sysusers;
--Database level perms;
PRINT '--Set Database level Permissions';
DECLARE _databaselevelperms CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT
sdp.state_desc,
sdp.permission_name
FROM
sys.database_permissions sdp WITH(NOLOCK)
JOIN sysusers su WITH(NOLOCK)
ON su.uid = sdp.grantee_principal_id
WHERE
su.name = @userName
AND sdp.class_desc = 'DATABASE';
OPEN _databaselevelperms;
FETCH NEXT FROM _databaselevelperms INTO @StateDesc, @PermissionName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @StateDesc + CHAR(13) + CHAR(9) + @PermissionName + CHAR(13) + CHAR(9)
+ 'TO ' + @userName + ';';
FETCH NEXT FROM _databaselevelperms INTO @StateDesc, @PermissionName
END
CLOSE _databaselevelperms;
DEALLOCATE _databaselevelperms;
SET @msgStatement = 'GO' + CHAR(13) + '--Set Object Specific Permissions'
PRINT @msgStatement;
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
SET @msgStatement = '';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 193
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 195
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 197
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 196
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 224
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,';
IF EXISTS(SELECT 1
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 ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO '
+ @DatabaseUserName + ';' ;
PRINT @msgStatement;
END
SET @msgStatement = '';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 193
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 195
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 197
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 196
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT 1
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 ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO '
+ @DatabaseUserName + ';' ;
PRINT @msgStatement;
END
FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName;
END
CLOSE _sysobjects;
DEALLOCATE _sysobjects;
PRINT 'GO'
END
SET NOCOUNT OFF
RETURN 0
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_user_permissions_script_get]
-----------------------------------------------------------
-- OBJECT NAME :dbo.p_user_permissions_script_get
-- AUTHOR: EricHu
-- DATE: 01/05/2012
-- INPUT PARAMETERS:
@userName VARCHAR(500)
--
-- OUTPUT PARAMETERS: none
-- DEPENDENCIES: none
-- DESCRIPTION: Used to script out permissions for a given user
-- MODIFICATION HISTORY:
-------------------------------------------------------------
AS
SET NOCOUNT ON
DECLARE @DatabaseUserName [SYSNAME];
SET @DatabaseUserName = @userName;
DECLARE @errStatement VARCHAR(1000),
@msgStatement VARCHAR(1000),
@DatabaseUserID SMALLINT,
@ServerUserName SYSNAME,
@RoleName VARCHAR(1000),
@ObjectID INT,
@ObjectName VARCHAR(1000),
@StateDesc VARCHAR(1000),
@permissionName VARCHAR(1000)
SELECT @DatabaseUserID = su.[uid],
@ServerUserName = sl.[loginname]
FROM dbo.[sysusers] su
INNER JOIN [master].dbo.[syslogins] sl
ON su.[sid] = sl.[sid]
WHERE su.[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME()
+ CHAR(13) + 'Please provide the name of a current user in ' + DB_NAME()
+ ' you wish to script.'
RAISERROR(@errStatement,
16,
1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13)
+ '--Created At: ' + CONVERT(VARCHAR, GETDATE(), 100)
+ REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '') + CHAR(13)
+ '--Created By: ' + SUSER_NAME() + CHAR(13) + '--Add User To Database'
+ CHAR(13) + 'USE [' + DB_NAME() + ']' + CHAR(13)
+ 'EXEC [sp_grantdbaccess]' + CHAR(13) + CHAR(9)
+ '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) + CHAR(9)
+ '@name_in_db = ''' + @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 = '''
+ @RoleName + ''',' + CHAR(13) + CHAR(9) + '@membername = '''
+ @DatabaseUserName + '''' + ';' ;
PRINT @msgStatement
FETCH NEXT FROM _sysusers INTO @RoleName
END
CLOSE _sysusers;
DEALLOCATE _sysusers;
--Database level perms;
PRINT '--Set Database level Permissions';
DECLARE _databaselevelperms CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT
sdp.state_desc,
sdp.permission_name
FROM
sys.database_permissions sdp WITH(NOLOCK)
JOIN sysusers su WITH(NOLOCK)
ON su.uid = sdp.grantee_principal_id
WHERE
su.name = @userName
AND sdp.class_desc = 'DATABASE';
OPEN _databaselevelperms;
FETCH NEXT FROM _databaselevelperms INTO @StateDesc, @PermissionName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @StateDesc + CHAR(13) + CHAR(9) + @PermissionName + CHAR(13) + CHAR(9)
+ 'TO ' + @userName + ';';
FETCH NEXT FROM _databaselevelperms INTO @StateDesc, @PermissionName
END
CLOSE _databaselevelperms;
DEALLOCATE _databaselevelperms;
SET @msgStatement = 'GO' + CHAR(13) + '--Set Object Specific Permissions'
PRINT @msgStatement;
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
SET @msgStatement = '';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 193
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 195
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 197
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 196
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 224
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,';
IF EXISTS(SELECT 1
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 ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO '
+ @DatabaseUserName + ';' ;
PRINT @msgStatement;
END
SET @msgStatement = '';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 193
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 195
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 197
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,';
IF EXISTS(SELECT 1
FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 196
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT 1
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 ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO '
+ @DatabaseUserName + ';' ;
PRINT @msgStatement;
END
FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName;
END
CLOSE _sysobjects;
DEALLOCATE _sysobjects;
PRINT 'GO'
END
SET NOCOUNT OFF
RETURN 0
運(yùn)行結(jié)果實(shí)例如下：
EXEC [p_user_permissions_script_get] 'dbo'
--Security creation script for user sa
--Created At: 01 13 2012 4:37PM163729
--Created By: sa
--Add User To Database
USE [DB_TEST]
EXEC [sp_grantdbaccess]
@loginame = 'sa',
@name_in_db = 'dbo';
GO
--Add User To Roles
EXEC [sp_addrolemember]
@rolename = 'db_owner',
@membername = 'dbo';
--Set Database level Permissions
GRANT
CONNECT
TO dbo;
GO
--Set Object Specific Permissions
GO
作者：
RDIF
出處：
http://www.rzrgm.cn/huyong/
Email：
406590790@qq.com
QQ：
406590790
微信：
13005007127(同手機(jī)號(hào))
框架官網(wǎng)：
http://www.guosisoft.com/
http://www.rdiframework.net/
框架其他博客：
http://blog.csdn.net/chinahuyong
http://www.rzrgm.cn/huyong
國(guó)思RDIF開發(fā)框架
，
給用戶和開發(fā)者最佳的.Net框架平臺(tái)方案，為企業(yè)快速構(gòu)建跨平臺(tái)、企業(yè)級(jí)的應(yīng)用提供強(qiáng)大支持。
關(guān)于作者：系統(tǒng)架構(gòu)師、信息系統(tǒng)項(xiàng)目管理師、DBA。專注于微軟平臺(tái)項(xiàng)目架構(gòu)、管理和企業(yè)解決方案，多年項(xiàng)目開發(fā)與管理經(jīng)驗(yàn)，曾多次組織并開發(fā)多個(gè)大型項(xiàng)目，在面向?qū)ο?、面向服?wù)以及數(shù)據(jù)庫(kù)領(lǐng)域有一定的造詣。現(xiàn)主要從事基于
RDIF
框架的技術(shù)開發(fā)、咨詢工作，主要服務(wù)于金融、醫(yī)療衛(wèi)生、鐵路、電信、物流、物聯(lián)網(wǎng)、制造、零售等行業(yè)。
如有問題或建議，請(qǐng)多多賜教！
本文版權(quán)歸作者和CNBLOGS博客共有，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁(yè)面明顯位置給出原文連接，如有問題，可以通過微信、郵箱、QQ等聯(lián)系我，非常感謝。
浙公網(wǎng)安備 33010602011771號(hào)