Script Won't Run in Job But Runs in Query Analyzer

I have a script that is basically a cursor that uses
sp_addrolemember to add all logins on the server to the
db_datareader role in every database. I use a variable
for each login. The script is in a job and scheduled to
run daily. I receive the following error: Msg 913, Sev
16: Could not find database ID %. Database may not be
activated yet or may be in transition. The problem is
that the EXACT same script will run in Query Analyzer with
NO errors. Why wouldn't the script give the same error in
Query Analyzer?
Thanks.I would have to see the script in order to (possibly) suggest anything...
:-)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Brandon Tolleson" <anonymous@.discussions.microsoft.com> wrote in message
news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...

quote:

---

> I have a script that is basically a cursor that uses
> sp_addrolemember to add all logins on the server to the
> db_datareader role in every database. I use a variable
> for each login. The script is in a job and scheduled to
> run daily. I receive the following error: Msg 913, Sev
> 16: Could not find database ID %. Database may not be
> activated yet or may be in transition. The problem is
> that the EXACT same script will run in Query Analyzer with
> NO errors. Why wouldn't the script give the same error in
> Query Analyzer?
> Thanks.

---

|||DECLARE @.lname varchar(100)
DECLARE @.uname varchar(100)
Declare @.mystr varchar(1000)
Declare @.dbname varchar(100)
DECLARE MacroVal CURSOR FOR Select name from syslogins
where name like 'MyDomain\
%' order by name
OPEN MacroVal
FETCH NEXT FROM MacroVal INTO @.lname
WHILE(@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
Set @.uname = substring(@.lname,charindex('',@.lname)
+1,50)
DECLARE DBName CURSOR FOR Select name from
sysdatabases order by name
OPEN DBName
FETCH NEXT FROM DBName INTO @.dbname
WHILE(@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
Set @.mystr = 'USE '+@.dbname+' If not
exists (select * from sysusers where SUSER_SNAME(sid)
= ''' +@.lname+''') Exec
sp_grantdbaccess '''+@.lname+''', '''+@.uname+''''
if @.uname not like 'student%'
BEGIN
Print (@.mystr)
Exec (@.mystr)
Set @.mystr = 'USE '+@.dbname+' If
exists (select * from sysusers where SUSER_SNAME(sid)
= '''+@.lname+''' and name <> ''dbo'') BEGIN EXEC
sp_addrolemember ''db_datareader'', '''+@.uname+''' EXEC
sp_addrolemember''db_backupoperator'', '''+@.uname+''' END'
Print (@.mystr)
Exec (@.mystr)
END
END
FETCH NEXT FROM DBName INTO @.dbname
END
CLOSE DBName
DEALLOCATE DBName
END
FETCH NEXT FROM MacroVal INTO @.lname
END
CLOSE MacroVal
DEALLOCATE MacroVal
GO

quote:

---

>--Original Message--
>I would have to see the script in order to (possibly)

---

suggest anything...

quote:

---

>:-)
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?

---

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

---

>
>"Brandon Tolleson" <anonymous@.discussions.microsoft.com>

---

wrote in message

quote:

---

>news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
with[QUOTE]
in[QUOTE]
>
>.
>

---

|||There doesn't seem to be a problem with the script per se. I just ran it on
my SQL Server and the
outcome was success. Did you specify an output file for the jobstep? This ca
n sometimes give more
info about the problem. In my output file, I only had messages such as:
USE master If not exists (select * from sysusers where SUSER_SNAME(sid)
= 'TIBORK\Tibor') Exec
sp_grantdbaccess 'TIBORK\Tibor', 'Tibor' [SQLSTATE 01000]
Granted database access to 'TIBORK\Tibor'. [SQLSTATE 01000]
You might want to go though the job in Agent so you didn't get any unfortuna
te linebreaks anywhere.
Although, the error message you posted didn't indicate that this is the prob
lem.
My guess is that some database isn't accessible when you run the script. Thi
s is what the error
message say. You might want to search KB for the error message. Or possibly
open a case with MS
support.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Brandon Tolleson" <anonymous@.discussions.microsoft.com> wrote in message
news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...

quote:

---

> I have a script that is basically a cursor that uses
> sp_addrolemember to add all logins on the server to the
> db_datareader role in every database. I use a variable
> for each login. The script is in a job and scheduled to
> run daily. I receive the following error: Msg 913, Sev
> 16: Could not find database ID %. Database may not be
> activated yet or may be in transition. The problem is
> that the EXACT same script will run in Query Analyzer with
> NO errors. Why wouldn't the script give the same error in
> Query Analyzer?
> Thanks.

---

|||I restarted the server and the job ran successfully. ?
Go figure.

quote:

---

>--Original Message--
>There doesn't seem to be a problem with the script per

---

se. I just ran it on my SQL Server and the

quote:

---

>outcome was success. Did you specify an output file for

---

the jobstep? This can sometimes give more

quote:

---

>info about the problem. In my output file, I only had

---

messages such as:

quote:

---

>USE master If not exists (select * from sysusers where

---

SUSER_SNAME(sid) = 'TIBORK\Tibor') Exec

quote:

---

>sp_grantdbaccess 'TIBORK\Tibor', 'Tibor' [SQLSTATE 01000]
>Granted database access to 'TIBORK\Tibor'. [SQLSTATE

---

01000]

quote:

---

>You might want to go though the job in Agent so you

---

didn't get any unfortunate linebreaks anywhere.

quote:

---

>Although, the error message you posted didn't indicate

---

that this is the problem.

quote:

---

>My guess is that some database isn't accessible when you

---

run the script. This is what the error

quote:

---

>message say. You might want to search KB for the error

---

message. Or possibly open a case with MS

quote:

---

>support.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?

---

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

---

>
>"Brandon Tolleson" <anonymous@.discussions.microsoft.com>

---

wrote in message

quote:

---

>news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
with[QUOTE]
in[QUOTE]
>
>.
>

---

Script Won't Run in Job But Runs in Query Analyzer

I have a script that is basically a cursor that uses
sp_addrolemember to add all logins on the server to the
db_datareader role in every database. I use a variable
for each login. The script is in a job and scheduled to
run daily. I receive the following error: Msg 913, Sev
16: Could not find database ID %. Database may not be
activated yet or may be in transition. The problem is
that the EXACT same script will run in Query Analyzer with
NO errors. Why wouldn't the script give the same error in
Query Analyzer?
Thanks.I would have to see the script in order to (possibly) suggest anything...
:-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Brandon Tolleson" <anonymous@.discussions.microsoft.com> wrote in message
news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
> I have a script that is basically a cursor that uses
> sp_addrolemember to add all logins on the server to the
> db_datareader role in every database. I use a variable
> for each login. The script is in a job and scheduled to
> run daily. I receive the following error: Msg 913, Sev
> 16: Could not find database ID %. Database may not be
> activated yet or may be in transition. The problem is
> that the EXACT same script will run in Query Analyzer with
> NO errors. Why wouldn't the script give the same error in
> Query Analyzer?
> Thanks.|||DECLARE @.lname varchar(100)
DECLARE @.uname varchar(100)
Declare @.mystr varchar(1000)
Declare @.dbname varchar(100)
DECLARE MacroVal CURSOR FOR Select name from syslogins
where name like 'MyDomain\
%' order by name
OPEN MacroVal
FETCH NEXT FROM MacroVal INTO @.lname
WHILE(@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
Set @.uname = substring(@.lname,charindex('\',@.lname)
+1,50)
DECLARE DBName CURSOR FOR Select name from
sysdatabases order by name
OPEN DBName
FETCH NEXT FROM DBName INTO @.dbname
WHILE(@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
Set @.mystr = 'USE '+@.dbname+' If not
exists (select * from sysusers where SUSER_SNAME(sid)
= ''' +@.lname+''') Exec
sp_grantdbaccess '''+@.lname+''', '''+@.uname+''''
if @.uname not like 'student%'
BEGIN
Print (@.mystr)
Exec (@.mystr)
Set @.mystr = 'USE '+@.dbname+' If
exists (select * from sysusers where SUSER_SNAME(sid)
= '''+@.lname+''' and name <> ''dbo'') BEGIN EXEC
sp_addrolemember ''db_datareader'', '''+@.uname+''' EXEC
sp_addrolemember''db_backupoperator'', '''+@.uname+''' END'
Print (@.mystr)
Exec (@.mystr)
END
END
FETCH NEXT FROM DBName INTO @.dbname
END
CLOSE DBName
DEALLOCATE DBName
END
FETCH NEXT FROM MacroVal INTO @.lname
END
CLOSE MacroVal
DEALLOCATE MacroVal
GO
>--Original Message--
>I would have to see the script in order to (possibly)
suggest anything...
>:-)
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Brandon Tolleson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
>> I have a script that is basically a cursor that uses
>> sp_addrolemember to add all logins on the server to the
>> db_datareader role in every database. I use a variable
>> for each login. The script is in a job and scheduled to
>> run daily. I receive the following error: Msg 913, Sev
>> 16: Could not find database ID %. Database may not be
>> activated yet or may be in transition. The problem is
>> that the EXACT same script will run in Query Analyzer
with
>> NO errors. Why wouldn't the script give the same error
in
>> Query Analyzer?
>> Thanks.
>
>.
>|||There doesn't seem to be a problem with the script per se. I just ran it on my SQL Server and the
outcome was success. Did you specify an output file for the jobstep? This can sometimes give more
info about the problem. In my output file, I only had messages such as:
USE master If not exists (select * from sysusers where SUSER_SNAME(sid) = 'TIBORK\Tibor') Exec
sp_grantdbaccess 'TIBORK\Tibor', 'Tibor' [SQLSTATE 01000]
Granted database access to 'TIBORK\Tibor'. [SQLSTATE 01000]
You might want to go though the job in Agent so you didn't get any unfortunate linebreaks anywhere.
Although, the error message you posted didn't indicate that this is the problem.
My guess is that some database isn't accessible when you run the script. This is what the error
message say. You might want to search KB for the error message. Or possibly open a case with MS
support.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Brandon Tolleson" <anonymous@.discussions.microsoft.com> wrote in message
news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
> I have a script that is basically a cursor that uses
> sp_addrolemember to add all logins on the server to the
> db_datareader role in every database. I use a variable
> for each login. The script is in a job and scheduled to
> run daily. I receive the following error: Msg 913, Sev
> 16: Could not find database ID %. Database may not be
> activated yet or may be in transition. The problem is
> that the EXACT same script will run in Query Analyzer with
> NO errors. Why wouldn't the script give the same error in
> Query Analyzer?
> Thanks.|||I restarted the server and the job ran successfully. ?
Go figure.
>--Original Message--
>There doesn't seem to be a problem with the script per
se. I just ran it on my SQL Server and the
>outcome was success. Did you specify an output file for
the jobstep? This can sometimes give more
>info about the problem. In my output file, I only had
messages such as:
>USE master If not exists (select * from sysusers where
SUSER_SNAME(sid) = 'TIBORK\Tibor') Exec
>sp_grantdbaccess 'TIBORK\Tibor', 'Tibor' [SQLSTATE 01000]
>Granted database access to 'TIBORK\Tibor'. [SQLSTATE
01000]
>You might want to go though the job in Agent so you
didn't get any unfortunate linebreaks anywhere.
>Although, the error message you posted didn't indicate
that this is the problem.
>My guess is that some database isn't accessible when you
run the script. This is what the error
>message say. You might want to search KB for the error
message. Or possibly open a case with MS
>support.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Brandon Tolleson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
>> I have a script that is basically a cursor that uses
>> sp_addrolemember to add all logins on the server to the
>> db_datareader role in every database. I use a variable
>> for each login. The script is in a job and scheduled to
>> run daily. I receive the following error: Msg 913, Sev
>> 16: Could not find database ID %. Database may not be
>> activated yet or may be in transition. The problem is
>> that the EXACT same script will run in Query Analyzer
with
>> NO errors. Why wouldn't the script give the same error
in
>> Query Analyzer?
>> Thanks.
>
>.
>

script to copy logins and users with all permissions

Hello all,
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.Inna,

If on SQL 2K, use Enterprise Manager.
Right click the database/All Tasks/Generate SQL Script.
Click the Options tab and you can select all of the logins and object
permissions you need.

-- Bill

"Inna" <mednyk@.hotmail.comwrote in message
news:1169753046.376196.16430@.v45g2000cwv.googlegro ups.com...

Quote:

Originally Posted by

Hello all,
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.
>

|||On Jan 25, 7:04 pm, "AlterEgo" <altereg...@.dslextreme.comwrote:

Quote:

Originally Posted by

Inna,
>
If on SQL 2K, use Enterprise Manager.
Right click the database/All Tasks/Generate SQL Script.
Click the Options tab and you can select all of the logins and object
permissions you need.
>
-- Bill
>
"Inna" <med...@.hotmail.comwrote in message
>
news:1169753046.376196.16430@.v45g2000cwv.googlegro ups.com...
>
>
>

Quote:

Originally Posted by

Hello all,
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.- Hide quoted text -

>
- Show quoted text -

Hello,
I cannot use Enterprise Manager, because every time it has to be done
for different database and this script is a part of another process
and it uses database name and server name as parameter.
Is there way just to populate script for any database, from query
analyzer?|||On Jan 30, 3:26 pm, "Inna" <med...@.hotmail.comwrote:

Quote:

Originally Posted by

On Jan 25, 7:04 pm, "AlterEgo" <altereg...@.dslextreme.comwrote:
>
>
>

Quote:

Originally Posted by

Inna,

>

Quote:

Originally Posted by

If on SQL 2K, use Enterprise Manager.
Right click the database/All Tasks/Generate SQL Script.
Click the Options tab and you can select all of the logins and object
permissions you need.

>

Quote:

Originally Posted by

-- Bill

>

Quote:

Originally Posted by

"Inna" <med...@.hotmail.comwrote in message

>

Quote:

Originally Posted by

news:1169753046.376196.16430@.v45g2000cwv.googlegro ups.com...

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Hello all,
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.- Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -

>
Hello,
I cannot use Enterprise Manager, because every time it has to be done
for different database and this script is a part of another process
and it uses database name and server name as parameter.
Is there way just to populate script for any database, from query
analyzer?

AlterEgo's point is - let Enterprise manager generate the sql for you.
It will give you a starting point and you can change the code to be
specific for your application.

Script to compare logins between 2 servers

Does anyone have the above in their code library? I need to compare logins between 2 servers in preparation to move a large number of DBs across.

Ideally, I am looking for duplicates.

In SQL Server 2000 you would query the syslogins table in the master database.

Code Snippet

USE master

GO

SELECT * FROM syslogins

In SQL Server 2005 you would use the new system information views. You can query the sys.syslogins view

Code Snippet

USE master

GO

SELECT * FROM sys.syslogins

Hope this helps get you on track. You can very easily write a comparison script and with a little work automate your findings.

NOTE: You can leave out the USE master - GO parts of the scripts for sql server 2005. the sys.syslogins view is available from all databases.

Script to compare logins between 2 servers

Does anyone have the above in their code library? I need to compare logins between 2 servers in preparation to move a large number of DBs across.

Ideally, I am looking for duplicates.

In SQL Server 2000 you would query the syslogins table in the master database.

Code Snippet

USE master

GO

SELECT * FROM syslogins

In SQL Server 2005 you would use the new system information views. You can query the sys.syslogins view

Code Snippet

USE master

GO

SELECT * FROM sys.syslogins

Hope this helps get you on track. You can very easily write a comparison script and with a little work automate your findings.

NOTE: You can leave out the USE master - GO parts of the scripts for sql server 2005. the sys.syslogins view is available from all databases.

Script for SQL 2005

Hi,

How to prepare a script that copies all the table structures, procedures, logins, functions from sql server 2000 database to sql server 2005 database. Sorry i am confused doing this. can anyone help me out. pls.

Thanks in advance

1st question, why do you need a script to do this? You can backup your SQL 2000 DB, restore it on SQL 2005 and upgrade it to SQL 2005 when you restore. Same holds true for detaching it from SQL 2000, copying it to SQL 2005 server, then attaching it to the SQL 2005 DB server as an upgraded DB.

If you need the scripts, you can script the database by Opening Enterprise Manager, right clicking on the database you want, then select script menu (I can't remember the exact name of the choice), but in the script wizard, select all objects and set your paramaters and file output. Run the wizard and you should get one or several (depending on how you set it up) files you can open and run on SQL 2005 (SQL 2005 supports SQL 2000 database compatability, so you can create the SQL 2000 DB on it and then upgrade it).

Script for SQL 2005

Hi,

How to prepare a script that copies all the table structures, procedures, logins, functions from sql server 2000 database to sql server 2005 database. Sorry i am confused doing this. can anyone help me out. pls.

Thanks in advance

1st question, why do you need a script to do this? You can backup your SQL 2000 DB, restore it on SQL 2005 and upgrade it to SQL 2005 when you restore. Same holds true for detaching it from SQL 2000, copying it to SQL 2005 server, then attaching it to the SQL 2005 DB server as an upgraded DB.

If you need the scripts, you can script the database by Opening Enterprise Manager, right clicking on the database you want, then select script menu (I can't remember the exact name of the choice), but in the script wizard, select all objects and set your paramaters and file output. Run the wizard and you should get one or several (depending on how you set it up) files you can open and run on SQL 2005 (SQL 2005 supports SQL 2000 database compatability, so you can create the SQL 2000 DB on it and then upgrade it).

script for login transfer from one phy server to another

Hi,

Do you have any idea about the procedure for transferring the logins and password from source server to the destination server.

scenario is as follows

serv - A wanto transfer to ms sql 2000 server - B
running on 6.5 ------------> will be sql2k
(Old server) (new server)

will appriciate if any weblink containg the check list.

with best regards

ranjanI have a script that generates SQL for recreating logins, user, roles, and permissions on SQL 2000 databases. It may run on your 6.5 database.
It will not, however, restore passwords, and I'm not sure you can do this.

Have you thought about running the upgrade on your 6.5 databases (or a copy of them)? It's been a while, but I think this retains the old passwords.

blindman|||Thanx for reply,

Detail of my upgradation plan -

- I am planning to upgrade my db server (mssql-6.5) to server
(mssql2k) on the same machine.

- I have prepared the checklist for that , Only thing I want is a script
which will take care of transferring my ( login name & passoword)
from 6.5 server to 2000 server.

- will appriciate if you can help me in writting the script or foreward a
readymade script.

thanx regards

Ranjan|||Sorry, but I don't have a script for that. Are you sure that the upgrade process won't do this?
As a matter of fact, I think Microsoft changed its encryption algorythm between versions 6.5 and 2000, so even if you copied the encrypted value to the new server it might not recognize your password. If you are looking for a method of decrypting the password, I don't have one but I know it has been cracked (the algorythm was not very strong). You can probably find code for decrypting it on the web.

blindman|||Refer to this KBA (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q246/1/33.ASP&NoWebContent=1) to accomplish the task.