Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Add db_datareader role membership recursively

Posted on 2010-09-20
9
Medium Priority
?
791 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 70

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 70

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 70

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

572 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