代码语言
.
CSharp
.
JS
Java
Asp.Net
C
MSSQL
PHP
Css
PLSQL
Python
Shell
EBS
ASP
Perl
ObjC
VB.Net
VBS
MYSQL
GO
Delphi
AS
DB2
Domino
Rails
ActionScript
Scala
代码分类
文件
系统
字符串
数据库
网络相关
图形/GUI
多媒体
算法
游戏
Jquery
Extjs
Android
HTML5
菜单
网页交互
WinForm
控件
企业应用
安全与加密
脚本/批处理
开放平台
其它
【
MSSQL
】
给定用户使用了的权限脚本
作者:
Dezai.CN
/ 发布于
2012/1/16
/
818
<div>SET ANSI_NULLS ON</div> <div>GO</div> <div>SET QUOTED_IDENTIFIER ON</div> <div>GO</div> <div>CREATE PROCEDURE [dbo].[p_user_permissions_script_get]</div> <div>-----------------------------------------------------------</div> <div>-- OBJECT NAME :dbo.p_user_permissions_script_get</div> <div>-- AUTHOR: EricHu</div> <div>-- DATE: 01/05/2012</div> <div>-- INPUT PARAMETERS: </div> <div> @userName VARCHAR(500)</div> <div>--</div> <div>-- OUTPUT PARAMETERS: none</div> <div>-- DEPENDENCIES: none</div> <div>-- DESCRIPTION: Used to script out permissions for a given user</div> <div>-- MODIFICATION HISTORY:</div> <div>-------------------------------------------------------------</div> <div>AS</div> <div></div> <div>SET NOCOUNT ON</div> <div></div> <div>DECLARE @DatabaseUserName [SYSNAME];</div> <div></div> <div>SET @DatabaseUserName = @userName;</div> <div></div> <div></div> <div>DECLARE @errStatement VARCHAR(1000),</div> <div> @msgStatement VARCHAR(1000),</div> <div> @DatabaseUserID SMALLINT,</div> <div> @ServerUserName SYSNAME,</div> <div> @RoleName VARCHAR(1000),</div> <div> @ObjectID INT,</div> <div> @ObjectName VARCHAR(1000),</div> <div> @StateDesc VARCHAR(1000),</div> <div> @permissionName VARCHAR(1000)</div> <div> </div> <div> </div> <div> </div> <div></div> SELECT @DatabaseUserID = su.[uid],</div> <div> @ServerUserName = sl.[loginname]</div> <div>FROM dbo.[sysusers] su</div> <div> INNER JOIN [master].dbo.[syslogins] sl</div> <div> ON su.[sid] = sl.[sid]</div> <div>WHERE su.[name] = @DatabaseUserName</div> <div></div> <div>IF @DatabaseUserID IS NULL</div> <div> BEGIN</div> <div> SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME()</div> <div> + CHAR(13) + 'Please provide the name of a current user in ' + DB_NAME()</div> <div> + ' you wish to script.'</div> <div></div> <div> RAISERROR(@errStatement,</div> <div> 16,</div> <div> 1)</div> <div> END</div> <div>ELSE</div> <div> BEGIN</div> <div> SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13)</div> <div> + '--Created At: ' + CONVERT(VARCHAR, GETDATE(), 100)</div> <div> + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '') + CHAR(13)</div> <div> + '--Created By: ' + SUSER_NAME() + CHAR(13) + '--Add User To Database'</div> <div> + CHAR(13) + 'USE [' + DB_NAME() + ']' + CHAR(13)</div> <div> + 'EXEC [sp_grantdbaccess]' + CHAR(13) + CHAR(9)</div> <div> + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) + CHAR(9)</div> <div> + '@name_in_db = ''' + @DatabaseUserName + '''' + ';'+ CHAR(13) + 'GO'</div> <div> + CHAR(13) + '--Add User To Roles'</div> <div></div> <div> PRINT @msgStatement</div> <div></div> <div> DECLARE _sysusers CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR</div> <div> SELECT [name]</div> <div> FROM [dbo].[sysusers]</div> <div> WHERE [uid] IN (SELECT [groupuid]</div> <div> FROM [dbo].[sysmembers]</div> <div> WHERE [memberuid] = @DatabaseUserID)</div> <div></div> <div> OPEN _sysusers</div> <div></div> <div> FETCH NEXT FROM _sysusers INTO @RoleName</div> <div></div> <div> WHILE @@FETCH_STATUS = 0</div> <div> BEGIN</div> <div> SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) + CHAR(9) + '@rolename = '''</div> <div> + @RoleName + ''',' + CHAR(13) + CHAR(9) + '@membername = '''</div> <div> + @DatabaseUserName + '''' + ';' ;</div> <div></div> <div> PRINT @msgStatement</div> <div></div> <div> FETCH NEXT FROM _sysusers INTO @RoleName</div> <div> END</div> <div> </div> <div> </div> <div> CLOSE _sysusers;</div> <div> </div> <div> DEALLOCATE _sysusers;</div> <div> </div> <div> --Database level perms;</div> <div> </div> <div> PRINT '--Set Database level Permissions';</div> <div> DECLARE _databaselevelperms CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR </div> <div> SELECT</div> <div> sdp.state_desc,</div> <div> sdp.permission_name</div> <div> FROM</div> <div> sys.database_permissions sdp WITH(NOLOCK)</div> <div> </div> <div> JOIN sysusers su WITH(NOLOCK)</div> <div> ON su.uid = sdp.grantee_principal_id</div> <div> </div> <div> WHERE</div> <div> su.name = @userName</div> <div> AND sdp.class_desc = 'DATABASE';</div> <div> </div> <div></div> <div> OPEN _databaselevelperms;</div> <div> </div> <div> FETCH NEXT FROM _databaselevelperms INTO @StateDesc, @PermissionName;</div> <div> </div> <div> WHILE @@FETCH_STATUS = 0</div> <div> BEGIN</div> <div> </div> <div> PRINT @StateDesc + CHAR(13) + CHAR(9) + @PermissionName + CHAR(13) + CHAR(9)</div> <div> + 'TO ' + @userName + ';';</div> <div> </div> <div> FETCH NEXT FROM _databaselevelperms INTO @StateDesc, @PermissionName</div> <div> </div> <div> END</div> <div> </div> <div> CLOSE _databaselevelperms;</div> <div></div> <div> DEALLOCATE _databaselevelperms;</div> <div></div> <div> SET @msgStatement = 'GO' + CHAR(13) + '--Set Object Specific Permissions'</div> <div></div> <div> PRINT @msgStatement;</div> <div></div> <div> DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR</div> <div> SELECT DISTINCT( [sysobjects].[id] ),</div> <div> '[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'</div> <div> FROM [dbo].[sysprotects]</div> <div> INNER JOIN [dbo].[sysobjects]</div> <div> ON [sysprotects].[id] = [sysobjects].[id]</div> <div> WHERE [sysprotects].[uid] = @DatabaseUserID;</div> <div></div> <div> OPEN _sysobjects;</div> <div></div> <div> FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName;</div> <div></div> <div> WHILE @@FETCH_STATUS = 0</div> <div> BEGIN</div> <div> SET @msgStatement = '';</div> <div></div> <div> IF EXISTS(SELECT 1</div> <div> FROM [dbo].[sysprotects]</div> <div> WHERE [id] = @ObjectID</div> <div> AND [uid] = @DatabaseUserID</div> <div> AND [action] = 193</div> <div> AND [protecttype] = 205)</div> <div> SET @msgStatement = @msgStatement + 'SELECT,';</div> <div></div> <div> IF EXISTS(SELECT 1</div> <div> FROM [dbo].[sysprotects]</div> <div> WHERE [id] = @ObjectID</div> <div> AND [uid] = @DatabaseUserID</div> <div> AND [action] = 195</div> <div> AND [protecttype] = 205)</div> <div> SET @msgStatement = @msgStatement + 'INSERT,';</div> <div></div> <div> IF EXISTS(SELECT 1</div> <div> FROM [dbo].[sysprotects]</div> <div> WHERE [id] = @ObjectID</div> <div> AND [uid] = @DatabaseUserID</div> <div> AND [action] = 197</div> <div> AND [protecttype] = 205)</div> <div> SET @msgStatement = @msgStatement + 'UPDATE,';</div> <div></div> <div> IF EXISTS(SELECT 1</div> <div> FROM [dbo].[sysprotects]</div> <div> WHERE [id] = @ObjectID</div> <div> AND [uid] = @DatabaseUserID</div> <div> AND [action] = 196</div> <div> AND [protecttype] = 205)</div> <div> SET @msgStatement = @msgStatement + 'DELETE,';</div> <div></div> <div> IF EXISTS(SELECT 1</div> <div> FROM [dbo].[sysprotects]</div> <div> WHERE [id] = @ObjectID</div> <div> AND [uid] = @DatabaseUserID</div> <div> AND [action] = 224</div> <div> AND [protecttype] = 205)</div> <div> SET @msgStatement = @msgStatement + 'EXECUTE,';</div> <div></div> <div> IF EXISTS(SELECT 1</div> <div> FROM [dbo].[sysprotects]</div> <div> WHERE [id] = @ObjectID</div> <div> AND [uid] = @DatabaseUserID</div> <div> AND [action] = 26</div> <div> AND [protecttype] = 205)</div> <div> SET @msgStatement = @msgStatement + 'REFERENCES,';</div> <div></div> <div> IF LEN(@msgStatement) > 0</div> <div> BEGIN</div> <div> IF RIGHT(@msgStatement, 1) = ','</div> <div> SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1);</div> <div></div> <div> SET @msgStatement = 'GRANT' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13)</div> <div> + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO '</div> <div> + @DatabaseUserName + ';' ;</div> <div></div> <div> PRINT @msgStatement;</div> <div> END</div> <div></div> <div> SET @msgStatement = '';</div> <div></div> <div> IF EXISTS(SELECT 1</div> <div> FROM [dbo].[sysprotects]</div> <div> WHERE [id] = @ObjectID</div> <div> AND [uid] = @DatabaseUserID</div> <div> AND [action] = 193</div> <div> AND [protecttype] = 206)</div> <div> SET @msgStatement = @msgStatement + 'SELECT,'</div> <div></div> <div> IF EXISTS(SELECT 1</div> <div> FROM [dbo].[sysprotects]</div> <div> WHERE [id] = @ObjectID</div> <div> AND [uid] = @DatabaseUserID</div> <div> AND [action] = 195</div> <div> AND [protecttype] = 206)</div> <div> SET @msgStatement = @msgStatement + 'INSERT,';</div> <div></div> <div> IF EXISTS(SELECT 1</div> <div> FROM [dbo].[sysprotects]</div> <div> WHERE [id] = @ObjectID</div> <div> AND [uid] = @DatabaseUserID</div> <div> AND [action] = 197</div> <div> AND [protecttype] = 206)</div> <div> SET @msgStatement = @msgStatement + 'UPDATE,';</div> <div></div> <div> IF EXISTS(SELECT 1</div> <div> FROM [dbo].[sysprotects]</div> <div> WHERE [id] = @ObjectID</div> <div> AND [uid] = @DatabaseUserID</div> <div> AND [action] = 196</div> <div> AND [protecttype] = 206)</div> <div> SET @msgStatement = @msgStatement + 'DELETE,'</div> <div></div> <div> IF EXISTS(SELECT 1</div> <div> FROM [dbo].[sysprotects]</div> <div> WHERE [id] = @ObjectID</div> <div> AND [uid] = @DatabaseUserID</div> <div> AND [action] = 224</div> <div> AND [protecttype] = 206)</div> <div> SET @msgStatement = @msgStatement + 'EXECUTE,';</div> <div></div> <div> IF EXISTS(SELECT *</div> <div> FROM [dbo].[sysprotects]</div> <div> WHERE [id] = @ObjectID</div> <div> AND [uid] = @DatabaseUserID</div> <div> AND [action] = 26</div> <div> AND [protecttype] = 206)</div> <div> SET @msgStatement = @msgStatement + 'REFERENCES,';</div> <div></div> <div> IF LEN(@msgStatement) > 0</div> <div> BEGIN</div> <div> IF RIGHT(@msgStatement, 1) = ','</div> <div> SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)</div> <div></div> <div> SET @msgStatement = 'DENY' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13)</div> <div> + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO '</div> <div> + @DatabaseUserName + ';' ;</div> <div></div> <div> PRINT @msgStatement;</div> <div> END</div> <div></div> <div> FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName;</div> <div> END</div> <div></div> <div> CLOSE _sysobjects;</div> <div></div> <div> DEALLOCATE _sysobjects;</div> <div> </div> <div> </div> <div></div> <div> PRINT 'GO'</div> <div> END</div> <div></div> <div></div> <div>SET NOCOUNT OFF</div> <div></div> <div>RETURN 0</div> <div> 运行结果实例如下：</div> <div>EXEC [p_user_permissions_script_get] 'dbo'</div> <div></div> <div></div> <div>--Security creation script for user sa</div> <div>--Created At: 01 13 2012 4:37PM163729</div> <div>--Created By: sa</div> <div>--Add User To Database</div> <div>USE [DB_TEST]</div> <div>EXEC [sp_grantdbaccess]</div> <div> @loginame = 'sa',</div> <div> @name_in_db = 'dbo';</div> <div>GO</div> <div>--Add User To Roles</div> <div>EXEC [sp_addrolemember]</div> <div> @rolename = 'db_owner',</div> <div> @membername = 'dbo';</div> <div>--Set Database level Permissions</div> <div>GRANT</div> <div> CONNECT</div> <div> TO dbo;</div> <div>GO</div> <div>--Set Object Specific Permissions</div> <div>GO</div>
试试其它关键字
权限脚本
同语言下
.
SQL查询 多列合并成一行用逗号隔开
.
查看存储过程修改时间,最近执行时间
.
设置手动批量删除数据库相关进程
.
获取某个表中特定字段的所有字符串形式
.
SQL 如何去除重复的字符串
.
怎么去掉一个字段中的重复数据
.
String 去除空格 回车 换行 水平制表符
.
SQL查询和替换含有回车，空格，TAB
.
SQL SERVER 查询每日新增用户数量、次留数量
.
判断两个字符串是否存在相同的内容
可能有用的
.
C#实现的html内容截取
.
List 切割成几份 工具类
.
SQL查询 多列合并成一行用逗号隔开
.
一行一行读取txt的内容
.
C#动态修改文件夹名称(FSO实现，不移动文件)
.
c# 移动文件或文件夹
.
c#图片添加水印
.
Java PDF转换成图片并输出给前台展示
.
网站后台修改图片尺寸代码
.
处理大图片在缩略图时的展示
Dezai.CN
贡献的其它代码
(
4037
)
.
多线程Socket服务器模块
.
生成随机密码
.
清除浮动样式
.
弹出窗口居中
.
抓取url的函数
.
使用base HTTP验证
.
div模拟iframe嵌入效果
.
通过header转向的方法
.
Session操作类
.
执行sqlite输入插入操作后获得自动编号的ID
Copyright © 2004 - 2024 dezai.cn. All Rights Reserved
站长博客
粤ICP备13059550号-3