February 8, 2008 at 8:33 am
Using Management Studio how do you script only user and object permissions? I don't want to script the corresponding "Create" statements for each object, only their permissions.
Thanks, Dave
February 8, 2008 at 9:25 am
Personally I use a tool called SQLPermissions. I'm not sure you can do what you are looking for through management studio. At least I havn't found a way yet.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 8, 2008 at 9:29 am
Some of the decisions Microsoft made with 2005 I just can't understand. In 2000 we have the ability to do this, yet it appears this functionality has been removed from 2005.
Tell me more about SQLPermissions if you don't mind. Is it inexpensive? Does it script object, role and user permissions?
Thanks, Dave
February 8, 2008 at 9:45 am
Not that this is much consolation - if you set the "IF NOT EXISTS" scripting option to true, it will include the CREATE syntax, but it will get bypassed on execute (since the objects exist).
Assuming you've then asked it to script user-level permissions and logins, that's the only part of the script that will actually fire.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 8, 2008 at 9:53 am
Yep. Thought about that, however I like to maintain the permissions in a separate script during certain migrations.
We found what appears to be a bug with Management Studio that I will post separately, but just to let you know if you have Windows XP - SP2 and Management Studio SP2, scripting will hang on databases with a large number of objects. I've let it run as long as 10 minutes beforing stopping the scripting process. Using Management Studio directly on the server, which is Windows 2003 SP2 and SQL 2005 SP1 the scripting completes in less then one minute. I'm not sure if this is a bug between SP1 and SP2 of SQL 2005 or a but related to XP SP2 and 2005 SP2. I did find a bug earlier in the year related to linked servers and that combination, which Microsoft confirmed as a new bug.
Dave
February 8, 2008 at 9:57 am
SQLPermissions is freeware made by Idera.
You can use it to script the permissions for all users on a database, one user on all databases, all users on all databases.
Its by no means perfect but for freeware its pretty darn good.
The only thing I have noticed is that it doesn't always script roles correctly.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 8, 2008 at 4:19 pm
you can use this simple script written by me to script your user and object permissions. the script is available in the link given below.
http://www.sql-articles.com/index.php?page=Scripts/scrip_user_05.php
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 9, 2008 at 3:07 pm
Thanks everyone
February 11, 2008 at 9:14 pm
The problem appears to be either a bug or very poor code on the part of Microsoft. When scripting object and user permissions with SP1, the options available are different then with SP2. In SP1 you only have the option of selecting CREATE or DROP statements. In SP2 you have the ability to select neither CREATE or DROP and script the permissions only. The problem I am ecountering is trying to script permissions for 2,000 to 3,000 objects (tables/views/stored procedures) and users/roles. When scripting permissions for just over 2,000 objects and users/roles the process does eventually complete after about 50 minutes. With 3,000 objects the scripting takes well over one hour to complete. Unfortunately I need to contact Microsoft to see if there is any way to speed things up. I doubt there is.
Dave
February 12, 2008 at 10:36 pm
See if this does what you want. It seem to work for me in SQL 2000 and 2005.
/* Script to Reverse Engineer SQL Server Object User Permissions */
/* source: http://www.sql-server-performance.com/bm_object_permission_scripts.asp */
-- Written By Bradley Morris
-- In Query Analyzer be sure to go to
-- Query -> Current Connection Options -> Advanced (Tab)
-- and set Maximum characters per column
-- to a high number, such as 10000, so
-- that all the code will be displayed.
DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName = 'put_user_name_here'
SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)
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 ' + @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(), 112) +
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
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 * 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 ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @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 ' + @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
Cheers.
G. Milner
March 4, 2008 at 11:35 am
I just wrote this quick little stored procedure to reverse engineer grants for a specific object. Note that I placed it in the master database so I can run it from any user database. I don't recommend this, but in this case I thought it was worth it.
USE master
GO
IF OBJECT_ID('dbo.sp_dba_ObjectPermissions', 'P') IS NOT NULL
BEGIN
DROP PROC dbo.sp_dba_ObjectPermissions
END
GO
CREATE PROC sp_dba_ObjectPermissions (
@as_ObjectNamesysname = NULL
)
AS
--
-- sp_dba_ObjectPermissions
--
-- Written by Aaron Sentell 03/04/2005
--
-- Reverse engineers all user privileges on the specified object (optional).
-- If no object is specified then all privileges on all objects in the
-- database are scripted. This script works only for grants at this point
-- and for SQL 2005 or later.
--
BEGIN
SET NOCOUNT ON
DECLARE @ls_crlf char(2)
SET @ls_crlf = CHAR(13) + CHAR(10)
SELECT sql = 'GRANT ' + permission_name + ' ON [' + OBJECT_SCHEMA_NAME(major_id) + '].[' + OBJECT_NAME(major_id) + '] TO ' + USER_NAME(grantee_principal_id) + @ls_crlf +
'GO' + @ls_crlf
FROM sys.database_permissions (NOLOCK)
WHERE major_id = ISNULL(OBJECT_ID(@as_ObjectName), major_id)
AND state = 'G'
AND OBJECT_SCHEMA_NAME(major_id) != 'SYS'
ORDER BY OBJECT_SCHEMA_NAME(major_id), OBJECT_NAME(major_id), USER_NAME(grantee_principal_id)
END
GO
-- Mark as a system stored procedure so we can execute it from any database
-- in the context of that database.
--
EXEC sys.sp_MS_marksystemobject sp_dba_ObjectPermissions
GO
March 4, 2008 at 12:00 pm
gdmilner (2/12/2008)
See if this does what you want. It seem to work for me in SQL 2000 and 2005.Cheers.
Thanks for the script G. I was looking for this kind for some time.
SQL DBA.
March 4, 2008 at 12:13 pm
Thanks. This will be helpful. I'm still going to contact Microsoft regarding what I believe to be a bug in their scripting process. It shouldn't take an hour to script out a database, even if the database contains over 3,000 objects. I'm curious if anyone else has experienced this problem.
742 tables
2,978 stored procedures
40 views
165 triggers
9 functions
578 primary keys
Takes almost one hour to return results on a 4 proc (3GHz), dual core server with 10 GB of memory and RAID 10 drives.
Dave
July 15, 2008 at 12:13 pm
I too have experienced ridiculous scripting times, so ridiculous that I cancelled the process that had been running for over 2 hours and generated it with SQL 2000 in about 30 seconds (I was scripting a SQL 2000 DB with Management Studio).
My complaints to MS have only been getting lip service. I am starting to consider MySQL.
Chris
July 15, 2008 at 12:17 pm
I still owe Microsoft a call on this one, but I don't expect any real help. I'm hoping 2008 will have addressed this issue, however that won't help our 2005 installations.
Dave
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply