Solved

Add db_datareader role membership recursively

Posted on 2010-09-20
9
781 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:
ScottPletcher 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:ScottPletcher
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
 
LVL 69

Expert Comment

by:ScottPletcher
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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:ScottPletcher
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:ScottPletcher
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now