Solved

Add db_datareader role membership recursively

Posted on 2010-09-20
9
782 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
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.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Login 17 37
Time Duration able to handle overflow of 24+ hours 5 39
Help with Sorting Full Text results 2 13
MS SQL Pivot table help 4 0
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

930 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

11 Experts available now in Live!

Get 1:1 Help Now