[推荐] 得到一个给定用户使用了的权限脚本

[推荐]得到一个给定用户使用了的权限脚本

——通过知识共享树立个人品牌。

得到一个给定用户使用了的权限脚本，直接上代码，自己研究。

SETANSI_NULLSON

GO
SETQUOTED_IDENTIFIERON
GO
CREATEPROCEDURE[dbo].[p_user_permissions_script_get]
-----------------------------------------------------------
--OBJECTNAME:dbo.p_user_permissions_script_get
--AUTHOR:EricHu
--DATE:01/05/2012
--INPUTPARAMETERS:
@userNameVARCHAR(500)
--
--OUTPUTPARAMETERS:none
--DEPENDENCIES:none
--DESCRIPTION:Usedtoscriptoutpermissionsforagivenuser
--MODIFICATIONHISTORY:
-------------------------------------------------------------
AS

SETNOCOUNTON

DECLARE@DatabaseUserName[SYSNAME];

SET@DatabaseUserName=@userName;


DECLARE@errStatementVARCHAR(1000),
@msgStatementVARCHAR(1000),
@DatabaseUserIDSMALLINT,
@ServerUserNameSYSNAME,
@RoleNameVARCHAR(1000),
@ObjectIDINT,
@ObjectNameVARCHAR(1000),
@StateDescVARCHAR(1000),
@permissionNameVARCHAR(1000)




SELECT@DatabaseUserID=su.[uid],
@ServerUserName=sl.[loginname]
FROMdbo.[sysusers]su
INNERJOIN[master].dbo.[syslogins]sl
ONsu.[sid]=sl.[sid]
WHEREsu.[name]=@DatabaseUserName

IF@DatabaseUserIDISNULL
BEGIN
SET@errStatement='User'+@DatabaseUserName+'doesnotexistin'+DB_NAME()
+CHAR(13)+'Pleaseprovidethenameofacurrentuserin'+DB_NAME()
+'youwishtoscript.'

RAISERROR(@errStatement,
16,
1)
END
ELSE
BEGIN
SET@msgStatement='--Securitycreationscriptforuser'+@ServerUserName+CHAR(13)
+'--CreatedAt:'+CONVERT(VARCHAR,GETDATE(),100)
+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')+CHAR(13)
+'--CreatedBy:'+SUSER_NAME()+CHAR(13)+'--AddUserToDatabase'
+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)+'--AddUserToRoles'

PRINT@msgStatement

DECLARE_sysusersCURSORLOCALFORWARD_ONLYREAD_ONLYFOR
SELECT[name]
FROM[dbo].[sysusers]
WHERE[uid]IN(SELECT[groupuid]
FROM[dbo].[sysmembers]
WHERE[memberuid]=@DatabaseUserID)

OPEN_sysusers

FETCHNEXTFROM_sysusersINTO@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

FETCHNEXTFROM_sysusersINTO@RoleName
END


CLOSE_sysusers;

DEALLOCATE_sysusers;

--Databaselevelperms;

PRINT'--SetDatabaselevelPermissions';
DECLARE_databaselevelpermsCURSORLOCALFORWARD_ONLYREAD_ONLYFOR
SELECT
sdp.state_desc,
sdp.permission_name
FROM
sys.database_permissionssdpWITH(NOLOCK)

JOINsysuserssuWITH(NOLOCK)
ONsu.uid=sdp.grantee_principal_id

WHERE
su.name=@userName
ANDsdp.class_desc='DATABASE';


OPEN_databaselevelperms;

FETCHNEXTFROM_databaselevelpermsINTO@StateDesc,@PermissionName;

WHILE@@FETCH_STATUS=0
BEGIN

PRINT@StateDesc+CHAR(13)+CHAR(9)+@PermissionName+CHAR(13)+CHAR(9)
+'TO'+@userName+';';

FETCHNEXTFROM_databaselevelpermsINTO@StateDesc,@PermissionName

END

CLOSE_databaselevelperms;

DEALLOCATE_databaselevelperms;

SET@msgStatement='GO'+CHAR(13)+'--SetObjectSpecificPermissions'

PRINT@msgStatement;

DECLARE_sysobjectsCURSORLOCALFORWARD_ONLYREAD_ONLYFOR
SELECTDISTINCT([sysobjects].[id]),
'['+USER_NAME([sysobjects].[uid])+'].['+[sysobjects].[name]+']'
FROM[dbo].[sysprotects]
INNERJOIN[dbo].[sysobjects]
ON[sysprotects].[id]=[sysobjects].[id]
WHERE[sysprotects].[uid]=@DatabaseUserID;

OPEN_sysobjects;

FETCHNEXTFROM_sysobjectsINTO@ObjectID,@ObjectName;

WHILE@@FETCH_STATUS=0
BEGIN
SET@msgStatement='';

IFEXISTS(SELECT1
FROM[dbo].[sysprotects]
WHERE[id]=@ObjectID
AND[uid]=@DatabaseUserID
AND[action]=193
AND[protecttype]=205)
SET@msgStatement=@msgStatement+'SELECT,';

IFEXISTS(SELECT1
FROM[dbo].[sysprotects]
WHERE[id]=@ObjectID
AND[uid]=@DatabaseUserID
AND[action]=195
AND[protecttype]=205)
SET@msgStatement=@msgStatement+'INSERT,';

IFEXISTS(SELECT1
FROM[dbo].[sysprotects]
WHERE[id]=@ObjectID
AND[uid]=@DatabaseUserID
AND[action]=197
AND[protecttype]=205)
SET@msgStatement=@msgStatement+'UPDATE,';

IFEXISTS(SELECT1
FROM[dbo].[sysprotects]
WHERE[id]=@ObjectID
AND[uid]=@DatabaseUserID
AND[action]=196
AND[protecttype]=205)
SET@msgStatement=@msgStatement+'DELETE,';

IFEXISTS(SELECT1
FROM[dbo].[sysprotects]
WHERE[id]=@ObjectID
AND[uid]=@DatabaseUserID
AND[action]=224
AND[protecttype]=205)
SET@msgStatement=@msgStatement+'EXECUTE,';

IFEXISTS(SELECT1
FROM[dbo].[sysprotects]
WHERE[id]=@ObjectID
AND[uid]=@DatabaseUserID
AND[action]=26
AND[protecttype]=205)
SET@msgStatement=@msgStatement+'REFERENCES,';

IFLEN(@msgStatement)>0
BEGIN
IFRIGHT(@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='';

IFEXISTS(SELECT1
FROM[dbo].[sysprotects]
WHERE[id]=@ObjectID
AND[uid]=@DatabaseUserID
AND[action]=193
AND[protecttype]=206)
SET@msgStatement=@msgStatement+'SELECT,'

IFEXISTS(SELECT1
FROM[dbo].[sysprotects]
WHERE[id]=@ObjectID
AND[uid]=@DatabaseUserID
AND[action]=195
AND[protecttype]=206)
SET@msgStatement=@msgStatement+'INSERT,';

IFEXISTS(SELECT1
FROM[dbo].[sysprotects]
WHERE[id]=@ObjectID
AND[uid]=@DatabaseUserID
AND[action]=197
AND[protecttype]=206)
SET@msgStatement=@msgStatement+'UPDATE,';

IFEXISTS(SELECT1
FROM[dbo].[sysprotects]
WHERE[id]=@ObjectID
AND[uid]=@DatabaseUserID
AND[action]=196
AND[protecttype]=206)
SET@msgStatement=@msgStatement+'DELETE,'

IFEXISTS(SELECT1
FROM[dbo].[sysprotects]
WHERE[id]=@ObjectID
AND[uid]=@DatabaseUserID
AND[action]=224
AND[protecttype]=206)
SET@msgStatement=@msgStatement+'EXECUTE,';

IFEXISTS(SELECT*
FROM[dbo].[sysprotects]
WHERE[id]=@ObjectID
AND[uid]=@DatabaseUserID
AND[action]=26
AND[protecttype]=206)
SET@msgStatement=@msgStatement+'REFERENCES,';

IFLEN(@msgStatement)>0
BEGIN
IFRIGHT(@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

FETCHNEXTFROM_sysobjectsINTO@ObjectID,@ObjectName;
END

CLOSE_sysobjects;

DEALLOCATE_sysobjects;



PRINT'GO'
END


SETNOCOUNTOFF

RETURN0

运行结果实例如下：

EXEC[p_user_permissions_script_get]'dbo'

--Securitycreationscriptforusersa
--Created At: 01 13 2012 4:37PM163729
--Created By:sa
-- AddUserToDatabase
USE[DB_TEST]
EXEC[sp_grantdbaccess]
@loginame= ' sa ',
@name_in_db= ' dbo ' ;
GO
-- AddUserToRoles
EXEC[sp_addrolemember]
@rolename= ' db_owner ',
@membername= ' dbo ' ;
--SetDatabaselevelPermissions
GRANT
CONNECT
TOdbo ;
GO
--SetObjectSpecificPermissions
GO

© 2011EricHu

原创作品，转贴请注明作者和出处，留此信息。

------------------------------------------------

cnBlobs:http://www.cnblogs.com/huyong/
优快云：http://blog.youkuaiyun.com/chinahuyong

作者：EricHu（DB、C\S、B\S、WebService、WCF、PM等）
出处：http://www.cnblogs.com/huyong/

Q Q：80368704 E-Mail: 80368704@qq.com
本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。
更多文章请看[置顶]索引贴——（不断更新中）