Solved

Add db_datareader role membership recursively

Posted on 2010-09-20
9
783 Views
Last Modified: 2012-06-21
Hi

Can someone give me a script that builds a tsql code to do the following

Add db_datareader role for a given login name - that I provide as a string to all  user databses that it finds on the server

The script should build a complete sql code with name of user and the name of the user databases it finds on the server embedded into the tsql - and then allow me to review it and execute it seperately.

It should be generic and perhaps flexible enough to run on 2k databases too if possible

we have a mixture of 2008, 2005 and 2k machines.

thanks

Mo



0
Comment
Question by:mooriginal
  • 5
  • 4
9 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 33717727
IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
    DROP TABLE #sql
CREATE TABLE #sql (
    dbname sysname,
    sql nvarchar(4000)
)

DECLARE @login sysname
SET @login = N'<your_login_name_goes_here>'


DECLARE @sql nvarchar(4000)
SET @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''replication'', ''reportserver'', ''reportservertempdb'', ''subscription'', ''tempdb'')
INSERT INTO #sql SELECT ''?'', ''
USE [?]
IF NOT EXISTS(SELECT 1 FROM [?].$owner$.sysusers WHERE sid = SUSER_SID(N''''' +
    @login + ''''')
    EXEC [?].dbo.sp_adduser N''''' + @login + ''''', N''''' + @login + '''''
EXEC [?].dbo.sp_addrolemember ''''db_datareader'''', N''''' + @login + '''''
''
'

SET @sql = REPLACE(@sql, '$owner$', CASE WHEN @@version LIKE '%2000%8.0%' THEN 'dbo' ELSE 'sys' END)

EXEC sp_MSforeachdb @sql

SELECT sql AS [--Sql_Cmds_To_Add_User_And_DbReader_Role]
FROM #sql
ORDER BY dbname

--DROP TABLE #sql
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 33717768
Btw, as usual for outputting scripts, you need to be in Text output mode (Ctrl-T) not Grid output mode (Ctrl-D).

You can omit the temp table and simple put out the code directly, but I prefer to see the output sorted by dbname -- sp_MSforeachdb annoyingly does not process in db name order.
0
 

Author Comment

by:mooriginal
ID: 33723864
thanks

Ive tried the script and its not working properly
ive attached a snippet of the results

It seems that some of the tsql produced is incomplete - whereas some are.

Also I was wanting to put go statements in there so it would look like this
just thought id mention that too in case you need to re-write the way its doing things
eg:

USE [?]
GO
IF NOT EXISTS(SELECT 1 FROM [?].$owner$.sysusers WHERE sid = SUSER_SID(N''''' +
    @login + ''''')
GO
    EXEC [?].dbo.sp_adduser N''''' + @login + ''''', N''''' + @login + '''''
GO
    EXEC [?].dbo.sp_addrolemember ''''db_datareader'''', N''''' + @login + '''''
''
'
USE [staff2_temp]
IF NOT EXISTS(SELECT 1 FROM [staff2_temp].sys.sysusers WHERE sid = SUSER_SID(N'mp_uk\testadmin')
    EXEC [staff2_temp].dbo.sp_adduser N'mp_uk\testadmin', N'mp_uk\testadmin'
EXEC [staff2_temp].dbo.sp_addrolemember 'db_datareader', N'

USE [test1]
IF NOT EXISTS(SELECT 1 FROM [test1].sys.sysusers WHERE sid = SUSER_SID(N'mp_uk\testadmin')
    EXEC [test1].dbo.sp_adduser N'mp_uk\testadmin', N'mp_uk\testadmin'
EXEC [test1].dbo.sp_addrolemember 'db_datareader', N'mp_uk\testadmin'

USE [Track_Master]
IF NOT EXISTS(SELECT 1 FROM [Track_Master].sys.sysusers WHERE sid = SUSER_SID(N'mp_uk\testadmin')
    EXEC [Track_Master].dbo.sp_adduser N'mp_uk\testadmin', N'mp_uk\testadmin'
EXEC [Track_Master].dbo.sp_addrolemember 'db_datareader'

USE [VQSM]
IF NOT EXISTS(SELECT 1 FROM [VQSM].sys.sysusers WHERE sid = SUSER_SID(N'mp_uk\testadmin')
    EXEC [VQSM].dbo.sp_adduser N'mp_uk\testadmin', N'mp_uk\testadmin'
EXEC [VQSM].dbo.sp_addrolemember 'db_datareader', N'mp_uk\testadmin'

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 33726413
I strongly suspect the truncation is a result of your output settings in QA.  Make sure you specify the maximum length of 8000 for text output and not the default of 256 (?, I think it's 256, could be somewhat more/less).

You can add the GOs easily enough, except in between the IF and the next statement.  Not sure why you want all those GOs -- I can understand one in between each db so the rest of the script continues even if one db gets an error.

Just change the line that defines the output:

DECLARE @sql nvarchar(4000)
SET @sql = N'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''replication'', ''reportserver'', ''reportservertempdb'', ''subscription'', ''tempdb'')
INSERT INTO #sql SELECT ''?'', ''
USE [?]
IF NOT EXISTS(SELECT 1 FROM [?].$owner$.sysusers WHERE sid = SUSER_SID(N''''' +
    @login + ''''')
    EXEC [?].dbo.sp_adduser N''''' + @login + ''''', N''''' + @login + N'''''
EXEC [?].dbo.sp_addrolemember ''''db_datareader'''', N''''' + @login + N'''''
GO
''

--added the GO keyword at end (also made the literals unicode by adding N to the literals)
'
0
 

Author Comment

by:mooriginal
ID: 33726519
thanks for that - the GO's are no biggie

Im still receiving the same problem after making the change you correctly identified.

The test im doing is on a 9.0 server using SQL 2008 QA

Here is the screenshot......

Its strange as its doing some lines less than others
eg again:

USE [IbisAEMP]
IF NOT EXISTS(SELECT 1 FROM [IbisAEMP].sys.sysusers WHERE sid = SUSER_SID(N'<your_login_name_goes_here>')
    EXEC [IbisAEMP].dbo.sp_adduser N'<your_login_name_goes_here>', N'<your_login_name_goes_here>'
EXEC [IbisAEMP].dbo.sp_addroleme

USE [IbisAEMP_Temp]
IF NOT EXISTS(SELECT 1 FROM [IbisAEMP_Temp].sys.sysusers WHERE sid = SUSER_SID(N'<your_login_name_goes_here>')
    EXEC [IbisAEMP_Temp].dbo.sp_adduser N'<your_login_name_goes_here>', N'<your_login_name_goes_here>'
EXEC [IbisAEMP_Te
QA-Screenshot.jpg
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 33727868
Pls post your (temp) table definition also.  Make sure the table column is defined as long enough as well.

Unless you need Unicode for your names on that server, switch to varchar and increase to 8000 -- won't hurt anything.

Collation issues are possible for a db or two, but I wouldn't expect it to affect a lot of them like that.  And you don't seem to be getting any msgs related to collation errors.
0
 

Author Comment

by:mooriginal
ID: 33732664
ok not sure what was going on but switching to varchar(8000) seems to have solved the problem...

thanks
0
 

Author Closing Comment

by:mooriginal
ID: 33732666
Good support through out process
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 33735200
Great, glad that worked.

I've seen Unicode do what seem to be strange things on concatenation if you mix Unicode and nonUnicode strings, which I did above.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 44
What is the proper way to use for criteria in left join? 7 27
SQL Server - Set Field Values ito Zero Based on Related Table 4 27
SQL Error - Query 6 26
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question