I was wondering if someone can provide some assistance. I need to do
the following:
-connect to the desire servers
-cycle thru the user database
-if desire databases exist:
--check to see if desire user is present in master..syslogins
--if the desire username is in master..syslogins
--go to that desire database and exec sp_grantdbaccess and
sp_addrolemember
-if username does not exist, exec sp_grantlogin 'domain\username',
switch to the desire database and exec sp_grantdbaccess and
sp_addrolemember
I have created the section where it cycles thru and look for user
databases using a while loop. However, I can't figure out how to make
the while loop look for specific database I need.
For example, I need to look for 4 databases (gg_ts, newsfeed, procMark,
and devEnv). I can make the while loop find these users database but
for each time the while loop loops thru, I need it to look for one of
those 4 database, if exist, store it in a variable and set that
variable so I can use that variable later on in the script.
I was wondering if anyone has a solution I can use or an
example/template?
Thanks.Is this code written in T-SQL or some application tier language (like .NET)?
FYI, the list of databases are present in the "sysdatabases" catalog
table in the "master" database (if you are using T-SQL) or in the
"Databases" collection if you are using SQL-DMO.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp|||hi, the code is written in T-SQL
I have a while loop looking thru the master..sysdatabases for user
databases. That portion, I am okay with. Its inside the while loop
that gets complicated. When the while loop find one of the above
databases, I want that database to be stored in a variable named
@.dbname sysname. Then I would include @.dbname into a portion of my
script, something like this:
select @.sql = @.dbname+'..sp_grantdbaccess <
username>
exec (@.sql)
at the moment, the way my script work is that it would go into every
database and add the desire user ... which isn't what I want :) . So I
made a lame script that looks something like this (without the while
loop):
code:
-- declaring a domain and a local userID, database ID, and a dbname
variable
declare @.duid varchar(40), @.dbid int, @.dbname sysname, @.sql
nvarchar(200), @.exist int
-- assigning values to the variables
select @.exist = 0
select @.duid = 'domain\username'
-- checking to see if the syslogins table in the master DB contains the
domain user
if exists (select name from master..syslogins where name = @.duid)
begin
print 'The domain user ' + @.duid + ' is present on this MSSQL
Instance.'
if exists (select name from master..sysdatabases where name =
'procMark')
begin
print 'database found ... switching to database'
if not exists (select name from procMark..sysusers where name =
@.duid)
begin print 'user is not present'
if exists (select name from tempdb..sysobjects where name like
'#result%')
begin
exec(N'drop table #result')
create table #result (result int)
select @.sql = 'select count(name) from procMark..sysusers where
name = '+''''+@.duid+''''
insert into #result exec(@.sql)
select @.exist = result from #result
if (@.exist) <
1
begin
print 'granting login to database'
exec procMark..sp_grantdbaccess @.duid, @.duid
exec procMark..sp_addrolemember 'RWXRole', @.duid
print 'permission granted to the database'
end
else print @.duid + ' has access to this database'
end
end
end
else
print 'database is not found'
end -- ending the primary if loop
else
print 'granting login to database and applying role'
exec sp_grantlogin @.duid
exec procMark..sp_grantdbaccess @.duid, @.duid
exec procMark..sp_addrolemember 'RWXRole', @.duid
print 'permission granted to the database'
The problem with the above code is ... I have to run this script
multiple times AND manually change the database name ... which I don't
want to do.|||>
select @.sql = @.dbname+'..sp_grantdbaccess <
username>
>
exec (@.sql)
Above is doable. Do you get errors from above code?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Oberion" <
xvoid@.blastos.com>
wrote in message
news:1129209468.203301.297950@.o13g2000cwo.googlegroups.com...
>
hi, the code is written in T-SQL
>
>
I have a while loop looking thru the master..sysdatabases for user
>
databases. That portion, I am okay with. Its inside the while loop
>
that gets complicated. When the while loop find one of the above
>
databases, I want that database to be stored in a variable named
>
@.dbname sysname. Then I would include @.dbname into a portion of my
>
script, something like this:
>
>
select @.sql = @.dbname+'..sp_grantdbaccess <
username>
>
exec (@.sql)
>
>
at the moment, the way my script work is that it would go into every
>
database and add the desire user ... which isn't what I want :) . So I
>
made a lame script that looks something like this (without the while
>
loop):
>
>
code:
>
>
-- declaring a domain and a local userID, database ID, and a dbname
>
variable
>
declare @.duid varchar(40), @.dbid int, @.dbname sysname, @.sql
>
nvarchar(200), @.exist int
>
>
-- assigning values to the variables
>
select @.exist = 0
>
select @.duid = 'domain\username'
>
>
-- checking to see if the syslogins table in the master DB contains the
>
domain user
>
if exists (select name from master..syslogins where name = @.duid)
>
begin
>
>
print 'The domain user ' + @.duid + ' is present on this MSSQL
>
Instance.'
>
>
if exists (select name from master..sysdatabases where name =
>
'procMark')
>
begin
>
print 'database found ... switching to database'
>
if not exists (select name from procMark..sysusers where name =
>
@.duid)
>
begin print 'user is not present'
>
if exists (select name from tempdb..sysobjects where name like
>
'#result%')
>
begin
>
exec(N'drop table #result')
>
create table #result (result int)
>
select @.sql = 'select count(name) from procMark..sysusers where
>
name = '+''''+@.duid+''''
>
insert into #result exec(@.sql)
>
select @.exist = result from #result
>
if (@.exist) <
1
>
begin
>
print 'granting login to database'
>
exec procMark..sp_grantdbaccess @.duid, @.duid
>
exec procMark..sp_addrolemember 'RWXRole', @.duid
>
print 'permission granted to the database'
>
end
>
else print @.duid + ' has access to this database'
>
end
>
end
>
end
>
else
>
print 'database is not found'
>
end -- ending the primary if loop
>
else
>
print 'granting login to database and applying role'
>
exec sp_grantlogin @.duid
>
exec procMark..sp_grantdbaccess @.duid, @.duid
>
exec procMark..sp_addrolemember 'RWXRole', @.duid
>
print 'permission granted to the database'
>
>
>
>
The problem with the above code is ... I have to run this script
>
multiple times AND manually change the database name ... which I don't
>
want to do.
>
|||yeah I know that work. The problem is running it with a while loop.
So when my while loop runs .. it looks at master, model, tempdb, msdb,
gg_ts, newsfeed, procMark, and devEnv.
Well when this while--loop loops thru each of these database, if gg_ts,
newsfeed, procMark, and devEnv db exist, store the name into the
@.dbname variable. That's the part I am having a problem with. If I
can figure this part out, I can use:
> select @.sql = @.dbname+'..sp_grantdbaccess <username>
> exec (@.sql)
but until I figure it out, the above doesn't work ...|||I'm not sure I understand your problem correctly. When you do the exists
clause, you can then do a statement like the following:
=====
SELECT @.dbname = name FROM master..sysdatabases WHERE name = 'xxx'
SET @.strSQL = 'USE ' + @.dbname
...
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Oberion" <xvoid@.blastos.com> wrote in message
news:1129222744.380113.4630@.g49g2000cwa.googlegroups.com...
> yeah I know that work. The problem is running it with a while loop.
> So when my while loop runs .. it looks at master, model, tempdb, msdb,
> gg_ts, newsfeed, procMark, and devEnv.
> Well when this while--loop loops thru each of these database, if gg_ts,
> newsfeed, procMark, and devEnv db exist, store the name into the
> @.dbname variable. That's the part I am having a problem with. If I
> can figure this part out, I can use:
>
> but until I figure it out, the above doesn't work ...
>|||SriSamp,
====
select @.dbname = name from master..sysdatabase'
set @.strSQL = 'use ' +@.dbname
====
If I use the above in my while-loop, it would insert all the database
name, once, into the @.dbname variable.
=====
SELECT @.dbname = name FROM master..sysdatabases WHERE name = 'xxx'
SET @.strSQL = 'USE ' + @.dbname
...
=====
With your code, I have to manually replace the 'xxx' with each of the 4
databases name. Is there a way to automatically populate the 4
databases name into the 'xxx' thru a variable that can how multiple
values? So it will automatically cycle thru however many database I
specify in one run instead of 4 or 5 or 6. I hope that made more sense
...|||Ok. Does this help?
=====
DECLARE allDatabases CURSOR FOR
SELECT name FROM master..sysdatabases
WHERE name IN (<your list> )
OPEN allDatabases
FETCH next FROM allDatabases INTO @.dbName
-- The while loop will ensure that you iterate through only your
-- list of databases since we provided them in the cursor above
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- do your stuff
FETCH next FROM allDatabases INTO @.dbName
END
CLOSE allDatabases
DEALLOCATE allDatabases
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Oberion" <xvoid@.blastos.com> wrote in message
news:1129250568.476223.245140@.z14g2000cwz.googlegroups.com...
> SriSamp,
> ====
> select @.dbname = name from master..sysdatabase'
> set @.strSQL = 'use ' +@.dbname
> ====
> If I use the above in my while-loop, it would insert all the database
> name, once, into the @.dbname variable.
> =====
> SELECT @.dbname = name FROM master..sysdatabases WHERE name = 'xxx'
> SET @.strSQL = 'USE ' + @.dbname
> ...
> =====
> With your code, I have to manually replace the 'xxx' with each of the 4
> databases name. Is there a way to automatically populate the 4
> databases name into the 'xxx' thru a variable that can how multiple
> values? So it will automatically cycle thru however many database I
> specify in one run instead of 4 or 5 or 6. I hope that made more sense
> ...
>|||SriSamp,
Thanks for the syntax, I will give that a try this afternoon and update
you.|||SriSamp, yes your syntax works great. I just need to rewrite my while
loop a bit.
Thanks for your help.
|||In addition and althrough already closed, I found something on my blog (didn′t know that I wrote such a thing yet :-) )