This procedure must be loaded into master. This will allow it to be used from any database.
1) To script all roles in MYDB, Exec MYDB..sp_ScriptRoles
2) To script role MyRole in MYDB, Exec MYDB..sp_ScriptRoles @Role='MyRole'
USE master
GO
if exists (select * FROM dbo.sysobjects where id = object_id(N'[dbo].[sp_ScriptRoles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_ScriptRoles]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_ScriptRoles]
@Role varchar(255) = '%'
-- Build statements to replicate authorities for Database Roles
--
-- This routine needs to be named 'sp_' so it can be hosted in master database and be able to script roles in any database
AS BEGIN
SET NOCOUNT ON
-- Display Audit information
PRINT '-- Generated on ' + Convert(char(20), Getdate(), 113) + ' by ' + SYSTEM_USER + ' on ' + @@servername
PRINT '-- Setup users, roles and privileges. sp_ScriptRoles v1.1'
-- Switch to current database
PRINT 'USE ' + db_name()
PRINT 'GO'
-- Add Users to Database
SELECT
'EXEC sp_grantdbaccess ''' + RTrim(l.name) + '''' as '-- Add Database Users'
FROM dbo.sysusers u
INNER JOIN master.dbo.syslogins l
ON u.sid = l.sid
WHERE u.islogin = 1
AND u.hasdbaccess = 1
AND u.name NOT IN ('dbo','guest')
AND u.name NOT LIKE '##%'
ORDER BY u.name
-- Create Database Roles
SELECT
'EXEC sp_addrole ''' + RTrim(r.name) + ''',dbo ' as '-- Create Roles'
FROM dbo.sysusers r
WHERE r.issqlrole = 1
AND r.gid > 0
AND r.name NOT IN ('RepositoryUser', 'TargetServersRole')
AND r.name LIKE @Role
ORDER BY r.name
-- Add Users to roles
SELECT
'EXEC sp_addrolemember ''' + RTrim(r.name) + ''',''' + RTrim(Coalesce(l.name,u.name)) + '''' as '-- Add Role Users'
FROM dbo.sysusers u
INNER JOIN sysmembers m
ON u.uid = m.memberuid
INNER JOIN sysusers r
ON m.groupuid = r.uid
LEFT OUTER JOIN master.dbo.syslogins l
ON u.sid = l.sid
WHERE r.issqlrole = 1
AND u.name <> 'dbo'
AND r.name LIKE '%'
ORDER BY r.name,u.name
-- Add Privileges to Roles
SELECT -- Object privileges
CASE
WHEN (p.protecttype = 204) OR (p.protecttype = 205) THEN
CASE
WHEN p.action = 26 THEN
'GRANT REFERENCES ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' TO ' + RTRIM(u.name)
WHEN p.action = 193 THEN
'GRANT SELECT ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' TO ' + RTRIM(u.name)
WHEN p.action = 195 THEN
'GRANT INSERT ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' TO ' + RTRIM(u.name)
WHEN p.action = 196 THEN
'GRANT DELETE ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' TO ' + RTRIM(u.name)
WHEN p.action = 197 THEN
'GRANT UPDATE ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' TO ' + RTRIM(u.name)
WHEN p.action = 224 THEN
'GRANT EXECUTE ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' TO ' + RTRIM(u.name)
END +
CASE
WHEN p.protecttype = 204 THEN
' WITH GRANT OPTION'
ELSE ''
END
WHEN p.protecttype = 206 THEN
CASE
WHEN p.action = 26 THEN
'DENY REFERENCES ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' TO ' + RTRIM(u.name)
WHEN p.action = 193 THEN
'DENY SELECT ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' TO ' + RTRIM(u.name)
WHEN p.action = 195 THEN
'DENY INSERT ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' TO ' + RTRIM(u.name)
WHEN p.action = 196 THEN
'DENY DELETE ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' TO ' + RTRIM(u.name)
WHEN p.action = 197 THEN
'DENY UPDATE ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' TO ' + RTRIM(u.name)
WHEN p.action = 224 THEN
'GRANT EXECUTE ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' TO ' + RTRIM(u.name)
END
END as '-- Setup Object privileges'
FROM dbo.sysobjects o
INNER JOIN sysusers s
ON o.uid = s.uid
INNER JOIN sysprotects p
ON o.id = p.id
INNER JOIN sysusers u
ON p.uid = u.uid
WHERE u.issqlrole = 1 -- Include Roles only
AND u.gid > 0 -- Exclude System Roles
AND u.name NOT IN ('RepositoryUser', 'TargetServersRole') -- Exclude Pseudo-system roles
AND u.name LIKE @Role
AND NOT (o.xtype = 'V' and o.category = 2) -- Exclude INFORMATION schema views
AND Coalesce(p.columns, 1) = 1 -- Exclude column-level privileges
ORDER BY u.name,s.name,o.name,p.action
SELECT -- Column privileges
CASE
WHEN (p.protecttype = 204) OR (p.protecttype = 205) THEN
CASE
WHEN p.action = 26 THEN
'GRANT REFERENCES ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' (' + RTRIM(c.name) + ') TO ' + RTRIM(u.name)
WHEN p.action = 193 THEN
'GRANT SELECT ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' (' + RTRIM(c.name) + ') TO ' + RTRIM(u.name)
WHEN p.action = 195 THEN
'GRANT INSERT ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' (' + RTRIM(c.name) + ') TO ' + RTRIM(u.name)
WHEN p.action = 196 THEN
'GRANT DELETE ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' (' + RTRIM(c.name) + ') TO ' + RTRIM(u.name)
WHEN p.action = 197 THEN
'GRANT UPDATE ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' (' + RTRIM(c.name) + ') TO ' + RTRIM(u.name)
END +
CASE
WHEN p.protecttype = 204 THEN
' WITH GRANT OPTION'
ELSE ''
END
WHEN p.protecttype = 206 THEN
CASE
WHEN p.action = 26 THEN
'DENY REFERENCES ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' (' + RTRIM(c.name) + ') TO ' + RTRIM(u.name)
WHEN p.action = 193 THEN
'DENY SELECT ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' (' + RTRIM(c.name) + ') TO ' + RTRIM(u.name)
WHEN p.action = 195 THEN
'DENY INSERT ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' (' + RTRIM(c.name) + ') TO ' + RTRIM(u.name)
WHEN p.action = 196 THEN
'DENY DELETE ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' (' + RTRIM(c.name) + ') TO ' + RTRIM(u.name)
WHEN p.action = 197 THEN
'DENY UPDATE ON ' + RTRIM(s.name) + '.' + RTRIM(o.name) + ' (' + RTRIM(c.name) + ') TO ' + RTRIM(u.name)
END
END as '-- Setup Column privileges'
FROM dbo.sysobjects o
INNER JOIN sysusers s
ON o.uid = s.uid
INNER JOIN sysprotects p
ON o.id = p.id
INNER JOIN sysusers u
ON p.uid = u.uid
INNER JOIN syscolumns c
ON c.id = o.id
AND c.id = p.id
INNER JOIN master.dbo.spt_values v
ON v.number = c.colid
WHERE u.issqlrole = 1 -- Include Roles only
AND u.gid > 0 -- Exclude System Roles
AND u.name NOT IN ('RepositoryUser', 'TargetServersRole') -- Exclude Pseudo-system roles
AND u.name LIKE @Role --and c.name = 'InstrumentID'
AND NOT (o.xtype = 'V' and o.category = 2) -- Exclude INFORMATION schema views
AND p.columns <> 1 -- Include only column-level privileges
AND CASE Substring(p.columns, 1, 1) & 1 -- Identify column for permission
WHEN 0 then Convert(tinyint, Substring(p.columns, v.low, 1))
ELSE (~Convert(tinyint, Coalesce(Substring(p.columns, v.low, 1),0)))
END & v.high <> 0
AND v.type = N'P'
ORDER BY u.name,s.name,o.name,p.action
PRINT '-- End of script'
END
GO 
Yan Pan explains how to set up proxies in SQL Server 2000, 2005 and 2008, and compares the differences between them
2008-12-30
4,305 reads
These scripts give you the list of logins created in the Last X Days for SQL Server 2000 or 2005.
2011-12-23 (first published: 2008-06-08)
2,339 reads
Ensure all your databases have standardised options by running this script weekly.
2011-09-19 (first published: 2007-12-17)
2,137 reads
Named or default instance, this will check info like Virtual Server Name, Cluster Name , Current owner, Preferred Owner? and? Status? (if clustered) and IP/Port for the current instance.
2011-12-15 (first published: 2007-10-11)
3,594 reads
In this presentation at the Jacksonville SQL Server Users Group, Bayer White playS the part of a developer protecting his application and Brian Knight attempts to hack his application using SQL Injection and cross-site scripting. Then, Bayer will show you how to protect yourself from the hacker and then Brian tries again. Back and forth the chess match goes until someone wins!
2007-02-12
5,265 reads