January 6, 2009 at 4:18 am
Have tried to find an easy way to do this but to no avail. I apologise as I am no DBA just a developer who is left propoing up a load of SQL Servers.
One such SQL instance has a load of SQL databases of which user A has a range of access across a lot of these databases. User B has just come along and I need to be able to mirror User A.
Is there please an easy way of doing this as this is something that is going to have to be repeated loads of times. I do it by hand normally but as there are loads of databases on this particular instance this is just too much.
Many Thanks.
Mark.
P.S apologies for the newbie question, I have searched but no luck finding anything for multiple databases.
January 6, 2009 at 5:21 am
Use the below mentioned stored procedure and replace test2 with the required loginname.
Create PROC usp_sql_audit
AS
SET NOCOUNT ON
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
DROP TABLE ##Users;
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
DROP TABLE ##DBUsers;
DECLARE @DBName VARCHAR(32);
DECLARE @SQLCmd VARCHAR(1024);
CREATE TABLE ##Users (
[sid] varbinary(85) NULL,
[Login Name] nvarchar(128) NULL,
[Default Database] sysname NULL,
[Login Type] varchar(9),
[AD Login Type] varchar(8),
[sysadmin] varchar(3),
[securityadmin] varchar(3),
[serveradmin] varchar(3),
[setupadmin] varchar(3),
[processadmin] varchar(3),
[diskadmin] varchar(3),
[dbcreator] varchar(3),
[bulkadmin] varchar(3))
---------------------------------------------------------
INSERT INTO ##Users
SELECT sid,
loginname AS [Login Name],
dbname AS [Default Database],
CASE isntname WHEN 1 THEN 'AD Login' ELSE 'SQL Login' END AS [Login Type],
CASE WHEN isntgroup = 1 THEN 'AD Group' WHEN isntuser = 1 THEN 'AD User' ELSE '' END AS [AD Login Type],
CASE [sysadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [sysadmin],
CASE [securityadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [securityadmin],
CASE [serveradmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [serveradmin],
CASE [setupadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [setupadmin],
CASE [processadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [processadmin],
CASE [diskadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [diskadmin],
CASE [dbcreator] WHEN 1 THEN 'Yes' ELSE 'No' END AS [dbcreator],
CASE [bulkadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [bulkadmin]
FROM master.dbo.syslogins
where loginname='test2';
---------------------------------------------------------
SELECT [Login Name],
[Default Database],
[Login Type],
[AD Login Type],
[sysadmin],
[securityadmin],
[serveradmin],
[setupadmin],
[processadmin],
[diskadmin],
[dbcreator],
[bulkadmin]
FROM ##Users
ORDER BY [Login Type],[AD Login Type],[Login Name]
CREATE TABLE ##DBUsers (
[Database User ID] nvarchar(1024),
[Server Login] nvarchar(1024),
[Database Role] nvarchar(1024),
[Database] sysname)
DECLARE csrDB CURSOR FOR
SELECT name
FROM master..sysdatabases
OPEN csrDB
FETCH NEXT
FROM csrDB
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLCmd = 'INSERT INTO ##DBUsers
SELECT su.[Name] as [Database User ID],
COALESCE (u.[Login Name], ''**Orphaned**'') as [Server Login],
COALESCE (sug.[name], ''Public'') AS [Database Role],
''' + @DBName + ''' as [Database]
FROM [' + @DBName + '].[dbo].[sysusers] su
LEFT OUTER JOIN ##Users u
ON su.sid = u.sid
LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm
INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug
ON sm.groupuid = sug.uid)
ON su.uid = sm.memberuid
WHERE su.hasdbaccess = 1
AND su.[name] != ''dbo'' and su.name=''test2'''
EXEC (@SQLCmd)
FETCH NEXT
FROM csrDB
INTO @DBName
END
CLOSE csrDB
DEALLOCATE csrDB
SELECT *
FROM ##DBUsers
ORDER BY [Database User ID],[Database];
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
DROP TABLE ##Users;
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
DROP TABLE ##DBUsers;
GO
HTH,
MJ
January 6, 2009 at 6:33 am
For object level permissions, replace testing with the username you want to script out permissions:-
SET NOCOUNT ON
-- declare all variables
DECLARE @DBName SYSNAME
DECLARE @sql nvarchar(max)
DECLARE @sSQL1 Varchar(30)
DECLARE @iRowCount INT
DECLARE @t_TableNames_Temp TABLE
(Database_name SYSNAME)
DROP TABLE TEST2
CREATE TABLE TEST2
(
SchemaName SYSNAME,
Object SYSNAME,
Username SYSNAME,
permissions_type SYSNAME,
permission_name SYSNAME,
permission_state SYSNAME,
state_desc SYSNAME,
Generated_Code SYSNAME
)
INSERT @t_TableNames_Temp
SELECT name
FROM master..sysdatabases
ORDER BY name
--Getting row count from table
SELECT @iRowCount = COUNT(*) FROM @t_TableNames_Temp
WHILE @iRowCount > 0
BEGIN
SELECT @DBName = Database_name from @t_TableNames_Temp
Set @sql=
'SELECT C.name ''Schema'',
B.name AS Object,
D.name username,
A.type permissions_type,
A.permission_name,
A.state permission_state,
A.state_desc,
state_desc
+ '' '' + permission_name
+ '' ON [''+ C.name
+ ''].['' + B.name + ''] to [''
+ D.name
+ '']'' COLLATE LATIN1_General_CI_AS AS
Generated_Code
FROM '+@Dbname+ '.sys.database_permissions AS A JOIN ' +@Dbname+'.sys.objects AS B ON
A.major_id =
B.object_id
JOIN ' +@Dbname+'.sys.schemas AS C ON B.schema_id =
C.schema_id
JOIN ' +@Dbname+'.sys.database_principals AS D ON
A.grantee_principal_id = D.principal_id
where D.name=''testing''
ORDER BY 1, 2, 3, 5'
Insert test2
Exec (@SQL)
DELETE FROM @t_TableNames_Temp WHERE @DBName = Database_name
SELECT @iRowCount = @iRowCount - 1
END
SELECT * FROM test2
SET NOCOUNT OFF
GO
MJ
January 6, 2009 at 7:17 am
Manu,
thanks. I have looked through the posts and will try them later. I must mneed to do more work with SQL as I could not see how either post updated any permissions. Certainly the first post appears to capture the details but could not see where the second login was altered to match the first.
Many Thanks.
Mark.
January 8, 2009 at 7:25 am
The long term solution is to use roles.
Instead of assigning permissions to individual users, create a role that has all of the permissions desired and assign the user to that role. Then when a new user comes along, all you have to do is assign that user to the role and your done.
It will take some time to set it up but it is well worth the effort in the long run.
Steve
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply