stored proc to copy a users permissions for a new user 1

[mflancour]

I'm guessing there is a really easy way to do this, but I don't know how. Basically I want to be able to with a stored proc specify user (windows authentication), and/or database, and/or server, and/or table, etc.....

and step one would return that users rights.

step 2 (likly a seperate proc) if I choose, would copy the existing user to a new user with a veriable for userid.....

 

[mrdenny]

Here is some code which will show the permissions granted to a user, as well as the code used to grant the rights. Simply copy the last column to notepad, and switch out the users account with the new username. If you have to do this often, create a role, grant the rights to the role, then simply put new users into the role.

This is for SQL 7 and SQL 2000.

select 
 user_name(p.grantor) as GRANTOR
 ,user_name(p.uid)  as GRANTEE
 ,db_name()    as TABLE_CATALOG
 ,user_name(o.uid)  as TABLE_SCHEMA
 ,o.name     as TABLE_NAME
 ,case p.action  
  when 26  then 'REFERENCES'
  when 193 then 'SELECT'
  when 195 then 'INSERT'
  when 196 then 'DELETE'
  when 197 then 'UPDATE'
  when 224 then 'EXECUTE'
	else 'test'
 end      as PRIVILEGE_TYPE,
	o.xtype
 ,case 
  when p.protecttype = 205 then 'NO'
  else 'YES'
 end      as IS_GRANTABLE,
	'grant ' + case p.action  
  when 26  then 'REFERENCES'
  when 193 then 'SELECT'
  when 195 then 'INSERT'
  when 196 then 'DELETE'
  when 197 then 'UPDATE'
  when 224 then 'EXECUTE'
	else 'test'
 end + ' on [' + user_name(o.uid) + '].[' + o.name + '] to [' + user_name(p.uid) + ']'
 from 
 sysprotects p, 
 sysobjects o
where  
  (p.protecttype = 204 or  /*grant exists without same grant with grant */
 (p.protecttype = 205
  and not exists(select * from sysprotects p2
    where p2.id = p.id and
    p2.uid = p.uid and 
    p2.action = p.action and 
    p2.columns = p.columns and
    p2.grantor = p.grantor and
    p2.protecttype = 204)))
  and p.action in (26,193,195,196,197,224)
  and p.id = o.id

  and 0 != (permissions(o.id)
	
)

order by table_name

This is for SQL 2005

select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
	sys.database_permissions.permission_name, 
	sys.database_permissions.state permission_state,
	sys.database_permissions.state_desc,
	state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id = 
	sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id = 
	sys.database_principals.principal_id
order by 1, 2, 3, 5

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mflancour]

Wow, sweet query. only thing I don't understand is that my user name is no where in the results. How do I look at what's set up for the windows user accounts?

 

[mflancour]

hey, forget that last post. just realized the database i ran this on all the windows accounts were assigned only rolls. Works great in another database where this isn't the case...now I just need to figure out how to make this query the info for all databases on the server

 

[mrdenny]

You can either use the procedure sp_MSforeachdb to run the code against each database.

Or manually run it for each database.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mflancour]

I'm very close now thanks to your help, the one hurdle left is trying to gather this same info from other servers. What I've been trying to do so far is query the linked server table to get a list of servers, then I cursor through that running this query and dumping the data into a temp table. Problem is, it only every queries the current server ....here is the code..I'm a bit perplexed...

DECLARE @v_SQL NVARCHAR(4000)
SET @v_SQL = 'select SO.name AS ObjectName,
SU.name AS UserName,
CASE
WHEN action = 193 then ''SELECT''
WHEN action = 195 then ''INSERT''
WHEN action = 196 then ''DELETE''
WHEN action = 197 then ''UPDATE''
WHEN action = 224 then ''EXECUTE''
ELSE ''SOME OTHER RIGHT''
END ''Action'',
CASE WHEN protecttype = 204 then ''GRANT_W_GRANT''
WHEN protecttype = 205 then ''GRANT''
WHEN protecttype = 206 then ''DENY''
END ''AccessType'', ''[SERVER2]'' as Server, ''[DB2]'' as DatabaseName
FROM [SERVER2].[DB2].dbo.sysprotects SP
JOIN [SERVER2].[DB2].dbo.sysobjects SO on SP.id = SO.id
JOIN [SERVER2].[DB2].dbo.sysusers SU on SP.uid = SU.uid
WHERE
SU.name LIKE ''%USER1%''
ORDER BY UserName, Server, ObjectName, Action'
PRINT @v_SQL
EXEC [SERVER2].[DB2].dbo.sp_executesql @v_SQL

 

[mflancour]

The final (working) code, in case anyone is interested Also, if you see any way to improve it let me know.

DECLARE @vc_DBName VARCHAR(100)
DECLARE @v_SQL NVARCHAR(4000)
DECLARE @vc_SrvName VARCHAR(100)

CREATE TABLE #tempAllDatabases (Name VARCHAR(100))

CREATE TABLE #tempAllLinkedServers (SrvName VARCHAR(100))

CREATE TABLE #tempPermissions(
	[ObjectName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[UserName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ACTION] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AccessType] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SERVER] VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DatabaseName] VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

SET @v_SQL = N'INSERT INTO #tempAllLinkedServers SELECT SRV_NAME = srvname FROM master.dbo.sysservers ' +
	CASE 
		WHEN @Server = '' THEN ''
		ELSE 'WHERE srvname LIKE ''%' + @Server + '%'''
	END

PRINT @v_SQL

EXEC sp_executesql @v_SQL 

IF @@ROWCOUNT > 0
BEGIN
	DECLARE c_AllLinkedServers CURSOR FOR 
	SELECT * FROM #tempAllLinkedServers
	OPEN c_AllLinkedServers
	FETCH NEXT FROM c_AllLinkedServers INTO @vc_SrvName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @vc_SrvName <> 'LOCALHOST'
		BEGIN
			TRUNCATE TABLE #tempAllDatabases

			SET @v_SQL = N'INSERT INTO #tempAllDatabases SELECT name FROM ' + @vc_SrvName + '.master.dbo.sysdatabases ' +
				CASE 
					WHEN @Database = '' THEN ''
					ELSE 'WHERE srvname LIKE ''%' + @Database + '%'''
				END

			PRINT @v_SQL

			EXEC sp_executesql @v_SQL 

			IF @@ROWCOUNT > 0
			BEGIN
				DECLARE c_AllDatabases CURSOR FOR 
				SELECT name FROM #tempAllDatabases
				OPEN c_AllDatabases
				FETCH NEXT FROM c_AllDatabases INTO @vc_DBName
				WHILE @@FETCH_STATUS = 0
				BEGIN
					SET @v_SQL = 'INSERT INTO #tempPermissions select SO.name AS ObjectName,
							SU.name AS UserName,
							CASE 
								WHEN action = 193 then ''SELECT''
								WHEN action = 195 then ''INSERT''
								WHEN action = 196 then ''DELETE''
								WHEN action = 197 then ''UPDATE''
								WHEN action = 224 then ''EXECUTE''
								ELSE ''SOME OTHER RIGHT''
							END ''Action'',
							CASE WHEN protecttype = 204 then ''GRANT_W_GRANT''
								WHEN protecttype = 205 then ''GRANT''
								WHEN protecttype = 206 then ''DENY''
							END ''AccessType'', ''' + 
							@vc_SrvName + ''' as Server, ''' + 
							@vc_DBName + ''' as DatabaseName
						FROM [' + @vc_SrvName + '].[' + @vc_dbname + '].dbo.' + 'sysprotects SP
							JOIN [' + @vc_SrvName + '].[' + @vc_dbname + '].dbo.' + 'sysobjects  SO on SP.id = SO.id
							JOIN [' + @vc_SrvName + '].[' + @vc_dbname + '].dbo.' + 'sysusers  SU on SP.uid = SU.uid
						WHERE 
							SU.name LIKE ''%' + @UserID + '%''
							AND SO.name LIKE ''%' + @Object + '%''
						ORDER BY UserName, Server, ObjectName, Action'

					PRINT @v_SQL

					EXEC sp_executesql @v_SQL 

					FETCH NEXT FROM c_AllDatabases INTO @vc_DBName

				END--WHILE @@FETCH_STATUS = 0
				CLOSE c_AllDatabases
				DEALLOCATE c_AllDatabases
			END--IF @@ROWCOUNT > 0
			ELSE
			BEGIN
				PRINT 'The database"' + @Database + '" was not found.' 
			END

		END--IF @vc_SrvName <> 'LOCALHOST'

		FETCH NEXT FROM c_AllLinkedServers INTO @vc_SrvName
	END--WHILE @@FETCH_STATUS = 0

	CLOSE c_AllLinkedServers
	DEALLOCATE c_AllLinkedServers

	SELECT * FROM #tempPermissions 

	SELECT 'Use ' + DatabaseName + ' ' + AccessType + ' ' + Action + ' on ' + ObjectName + ' to [' + UserName + ']'  
	FROM #tempPermissions
END--IF @@ROWCOUNT > 0
ELSE
BEGIN
	PRINT 'The server "' + @Server + '" was not found.' 
END