Solved

Add db_datareader role membership recursively

Posted on 2010-09-20
9
785 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
disk usage reporting tools 27 56
T-SQL and CLR parameter strings 9 28
Not listening to where 1 22
Insert multiple records into a table 4 14
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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