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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2230
  • Last Modified:

sp_MSForEachDb

i am adding a number of diff users to a number of different db's.  i thought i'd speed things up by proceduralizing the whole thing.  i just want to pass in the
login(s) and have the given users added to the server (sp_grantlogin), then granted access (sp_grantdbaccess) to the user db's that are NOT in my list and added to the db_datareader role (sp_addrolemember).  right now i just wanted to do it manually and then wrap it up into a proc.  but, it's failing.  (a lot)
this is what i'm using.  my error is copied in below.  in this text I have replace the two logins i'm using with 'user1' and 'user2', i've also replace the 4 user db's i'm using with 'database1', 'database2', etc., and the domainname is changed to just that - 'DOMAINNAME'.   does anyboedy see what am i missing?

EXEC sp_MSForEachDb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''pubs'',''northwind'',''onemoredb'')
BEGIN
PRINT ''Adding logins to ?....''
exec sp_grantlogin ''DOMAINNAME\user1''
exec sp_grantlogin ''DOMAINNAME\user2''
PRINT ''Granting access to ? ...''
EXEC ?.dbo.sp_grantdbaccess ''DOMAINNAME\user1'', ''user1''
EXEC ?.dbo.sp_grantdbaccess ''DOMAINNAME\user2'', ''user2''
EXEC ?.dbo.sp_addrolemember ''db_datareader'',''DOMAINNAME\user1''
EXEC ?.dbo.sp_addrolemember ''db_datareader'',''DOMAINNAME\user2''
END --IF
'

Server: Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 66
User or role 'DOMAINNAME\user1' does not exist in this database.
Server: Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 66
User or role 'DOMAINNAME\user2' does not exist in this database.
Adding logins to database1....
Granted login access to 'DOMAINNAME\user1'.
Granted login access to 'DOMAINNAME\user2'.
Granting access to database1...
Granted database access to 'DOMAINNAME\user1'.
Granted database access to 'DOMAINNAME\user2'.
Server: Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 66
User or role 'DOMAINNAME\user1' does not exist in this database.
Server: Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 66
User or role 'DOMAINNAME\user2' does not exist in this database.
Adding logins to database2....
Granted login access to 'DOMAINNAME\user1'.
Granted login access to 'DOMAINNAME\user2'.
Granting access to database2 ...
Granted database access to 'DOMAINNAME\user1'.
Granted database access to 'DOMAINNAME\user2'.
Server: Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 66
User or role 'DOMAINNAME\user1' does not exist in this database.
Server: Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 66
User or role 'DOMAINNAME\user2' does not exist in this database.
Adding logins to database3....
Granted login access to 'DOMAINNAME\user1'.
Granted login access to 'DOMAINNAME\user2'.
Granting access to database3 ...
Granted database access to 'DOMAINNAME\user1'.
Granted database access to 'DOMAINNAME\user2'.
Server: Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 66
User or role 'DOMAINNAME\user1' does not exist in this database.
Server: Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 66
User or role 'DOMAINNAME\user2' does not exist in this database.
Adding logins to database4....
Granted login access to 'DOMAINNAME\user1'.
Granted login access to 'DOMAINNAME\user2'.
Granting access to database4 ...
Granted database access to 'DOMAINNAME\user1'.
Granted database access to 'DOMAINNAME\user2'.
0
dbaSQL
Asked:
dbaSQL
  • 12
  • 8
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

PRINT ''Adding logins ....''  -- << logins are created ONCE
exec sp_grantlogin ''DOMAINNAME\user1''
exec sp_grantlogin ''DOMAINNAME\user2''

EXEC sp_MSForEachDb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''pubs'',''northwind'',''onemoredb'')
BEGIN
PRINT ''Granting access to ? ...''
EXEC ?.dbo.sp_grantdbaccess ''DOMAINNAME\user1'', ''user1''
EXEC ?.dbo.sp_grantdbaccess ''DOMAINNAME\user2'', ''user2''
EXEC ?.dbo.sp_addrolemember ''db_datareader'',''user1''  --- << you have to use the USER name, not the LOGIN name
EXEC ?.dbo.sp_addrolemember ''db_datareader'',''user2''
END ' --IF   ---<< need the final single quote here...
0
 
dbaSQLAuthor Commented:
struggled for a bit as i spelled the user name wrong.
but, this works just fine.  thank you, angel.

let's say i code it to take a login name and a user name, how do i put the values in properly?
this is failing w/'count field incorrect or syntax error.  

drop proc dbo.usp_AddLogins
go
create proc dbo.usp_AddLogins (
 @login varchar(35),
 @username varchar(15)
)
AS

SET NOCOUNT ON

PRINT 'Adding logins ....'
  EXEC sp_grantlogin '@login'
GO

EXEC sp_MSForEachDb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''pubs'',''northwind'',''otherdb'')
BEGIN
PRINT 'Granting access to ? ...'
EXEC ?.dbo.sp_grantdbaccess '@login', '@user'
EXEC ?.dbo.sp_addrolemember ''db_datareader'','@user'
END --IF
'

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
like this:

drop proc dbo.usp_AddLogins
go
create proc dbo.usp_AddLogins (
 @login varchar(35),
 @username varchar(15)
)
AS

SET NOCOUNT ON

PRINT 'Adding logins ....'
  EXEC sp_grantlogin @login
GO

EXEC sp_MSForEachDb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''pubs'',''northwind'',''otherdb'')
BEGIN
PRINT 'Granting access to ? ...'
EXEC ?.dbo.sp_grantdbaccess ''' + @login + ''', ''' + @user + '''
EXEC ?.dbo.sp_addrolemember ''db_datareader'',''' + @user + '''
END --IF
'
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
dbaSQLAuthor Commented:
same error, angel:  [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

drop proc dbo.usp_AddLogins
go
create proc dbo.usp_AddLogins (
 @login varchar(35),
 @user varchar(15)
)
AS

SET NOCOUNT ON

PRINT 'Adding logins ....'
  EXEC sp_grantlogin @login
  EXEC sp_defaultdb @login
GO

EXEC sp_MSForEachDb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''pubs'',''northwind'',''otherdb'')
BEGIN
PRINT 'Granting access to ? ...'
EXEC ?.dbo.sp_grantdbaccess '''+ @login + ''',''' + @user + '''
EXEC ?.dbo.sp_addrolemember ''db_datareader'',''' + @user + '''
END '

SET NOCOUNT OFF
GO
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

drop proc dbo.usp_AddLogins
go
create proc dbo.usp_AddLogins (
 @login varchar(35),
 @user varchar(15)
)
AS

SET NOCOUNT ON

PRINT 'Adding logins ....'
  EXEC sp_grantlogin @login
  EXEC sp_defaultdb @login
GO

EXEC sp_MSForEachDb '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''pubs'',''northwind'',''otherdb'')
BEGIN
PRINT ''Granting access to ? ...''  --<<missing additional single quotes here
EXEC ?.dbo.sp_grantdbaccess '''+ @login + ''',''' + @user + '''
EXEC ?.dbo.sp_addrolemember ''db_datareader'',''' + @user + '''
END '

SET NOCOUNT OFF
GO
0
 
dbaSQLAuthor Commented:
new error:
(and thank you, angel, for noting specifically what you've changed...with the carrots <<<<  ....it is very insightful)

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '+'.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not sure where that could be, must be a missing ' I guess
0
 
dbaSQLAuthor Commented:
i'm just not finding it
0
 
dbaSQLAuthor Commented:
oh, angel, what of my EXEC sp_defaultdb @login, doesn't that need the db in there, too?  maybe that's it?
0
 
dbaSQLAuthor Commented:
nope, still firing the same error....but, what of my sp_defaultdb?  that is wrong, isn't it?
0
 
dbaSQLAuthor Commented:
angel, i tried this to handle the def db, it still fails, any thoughts at all?



drop proc dbo.usp_AddLogins
go
create proc dbo.usp_AddLogins (
 @login varchar(35),
 @user varchar(15),
 @db varchar(7) = 'defdb'
)
AS

SET NOCOUNT ON

PRINT 'Adding logins ....'
  EXEC sp_grantlogin @login
--  EXEC sp_defaultdb @login, @db

EXEC sp_MSForEachDb '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''pubs'',''northwind'',otherdb'')
BEGIN
PRINT ''Granting access to ? ...''
EXEC ?.dbo.sp_grantdbaccess '''+ @login + ''',''' + @user + '''
EXEC ?.dbo.sp_addrolemember ''db_datareader'',''' + @user + '''
END '

SET NOCOUNT OFF
GO
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:


drop proc dbo.usp_AddLogins
go
create proc dbo.usp_AddLogins (
 @login varchar(35),
 @user varchar(15),
 @db varchar(7) = 'defdb'
)
AS

SET NOCOUNT ON

PRINT 'Adding logins ....'
  EXEC sp_grantlogin @login
--  EXEC sp_defaultdb @login, @db

EXEC sp_MSForEachDb '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''pubs'',''northwind'',''otherdb'')  ---<< missing quotes before otherdb
BEGIN
PRINT ''Granting access to ? ...''
EXEC ?.dbo.sp_grantdbaccess '''+ @login + ''',''' + @user + '''
EXEC ?.dbo.sp_addrolemember ''db_datareader'',''' + @user + '''
END '

SET NOCOUNT OFF
GO
0
 
dbaSQLAuthor Commented:
i apologize, angel, that is there only as a type-o.  i cut/pasted the tsql, then i replaced the actual db name with 'otherdb'..and i inadvertently cut the 1st two single ticks.  i'm still having the 'incorrect syntax near '+' error, i've completely re-typed it twoce.  do you know the right way to incorporate the sp_defaultdb?  
0
 
dbaSQLAuthor Commented:
does it need to be EXEC master..sp_MSForEachDb ?
just reaching here, i need to get this corrected
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>EXEC sp_defaultdb @login, @db
is correct to incorporate the default login, but you need to put it AFTER the EXEC sp_MSForEachDb.
let's see if this makes any difference;

drop proc dbo.usp_AddLogins
go
create proc dbo.usp_AddLogins (
 @login varchar(35),
 @user varchar(15),
 @db varchar(7) = 'defdb'
)
AS
DECLARE @SQL NVARCHAR(1000)
SET NOCOUNT ON

SET @SQL = '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''pubs'',''northwind'',''otherdb'')
BEGIN
PRINT ''Granting access to ? ...''
EXEC ?.dbo.sp_grantdbaccess ''' + @login + ''',''' + @user + '''
EXEC ?.dbo.sp_addrolemember ''db_datareader'',''' + @user + '''
END '

PRINT 'Adding logins ....'
EXEC sp_grantlogin @login
EXEC sp_MSForEachDb @sql
EXEC sp_defaultdb @login, @db

SET NOCOUNT OFF
GO
0
 
dbaSQLAuthor Commented:
that's it.  wow.  angel, i'm not sure what i was missing?  simply the location of the defaultdb?  is that it?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no. in fact, the sp_MSForEachDb does not seem to like to have the argument build (+) on the fly.
0
 
dbaSQLAuthor Commented:
i'm not sure exactly what you're saying, angel.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is fine:
EXEC sp_MSForEachDb @sql

this is NOT working:
EXEC sp_MSForEachDb @sql1 + @sql2

0
 
dbaSQLAuthor Commented:
aah yes, i see that.  sorry, haven't had my coffee yet.
thank you, angel
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now