default db permissions for account

Hi
I want to allow the windows iusr_computername account exec permissions on
user stored procs and select on views. I have 50 odd stored procs. Is there
a way of assigning permissions so that this account always has those
permissions and the permissions are automatically added when a new view or
sp is added?
I'd also like to easily transfer this to other databases. I'm sure the
answer lies in using roles or scripts or perhaps there's a fundamentally
easy way that I haven't found yet?
Thanks
AndrewHi Andrew,
There is no fundamentally easy way to assign permissions on all the stored
procedures to a user.
You can use the following script to give a user permission on all existing
stored procedures, but you have to re-run it to give permissions to newly
created stored procedures:
DECLARE @.proc_name SYSNAME
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME, 'IsMSShipped') = 0 -- Only
user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
EXEC ('GRANT EXECUTE ON ' + @.proc_name + ' TO MyUser')
END
You can use something similar to assign permissions on views using
inforamtion_schema.views.
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Andrew Jocelyn" <andrew.jocelyn@.REMOVETHISBITempetus.co.uk> wrote in
message news:OvwDb1ZXDHA.2424@.TK2MSFTNGP12.phx.gbl...
> Hi
> I want to allow the windows iusr_computername account exec permissions on
> user stored procs and select on views. I have 50 odd stored procs. Is
there
> a way of assigning permissions so that this account always has those
> permissions and the permissions are automatically added when a new view or
> sp is added?
> I'd also like to easily transfer this to other databases. I'm sure the
> answer lies in using roles or scripts or perhaps there's a fundamentally
> easy way that I haven't found yet?
> Thanks
> Andrew
>|||Hi thanks for that.
Just one little problem. I'm getting an error "Invalid parameter 2 specified
for object_id.". I'm afraid my attempts to debug have failed. Can you help?
Thanks again
Andrew
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:Oh%23e9saXDHA.1280@.tk2msftngp13.phx.gbl...
> Hi Andrew,
> There is no fundamentally easy way to assign permissions on all the stored
> procedures to a user.
> You can use the following script to give a user permission on all existing
> stored procedures, but you have to re-run it to give permissions to newly
> created stored procedures:
> DECLARE @.proc_name SYSNAME
> SET @.proc_name = ''
> WHILE 1=1
> BEGIN
> SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME, 'IsMSShipped') = 0 -- Only
> user stored procedures
> AND ROUTINE_TYPE = 'Procedure'
> AND ROUTINE_NAME > @.proc_name
> ORDER BY ROUTINE_NAME
> )
> IF @.proc_name IS NULL BREAK
> EXEC ('GRANT EXECUTE ON ' + @.proc_name + ' TO MyUser')
> END
> You can use something similar to assign permissions on views using
> inforamtion_schema.views.
>
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Andrew Jocelyn" <andrew.jocelyn@.REMOVETHISBITempetus.co.uk> wrote in
> message news:OvwDb1ZXDHA.2424@.TK2MSFTNGP12.phx.gbl...
> > Hi
> >
> > I want to allow the windows iusr_computername account exec permissions
on
> > user stored procs and select on views. I have 50 odd stored procs. Is
> there
> > a way of assigning permissions so that this account always has those
> > permissions and the permissions are automatically added when a new view
or
> > sp is added?
> >
> > I'd also like to easily transfer this to other databases. I'm sure the
> > answer lies in using roles or scripts or perhaps there's a fundamentally
> > easy way that I haven't found yet?
> >
> > Thanks
> > Andrew
> >
> >
>

Default Databases and Windows Groups

Hello!
I have a question about default databases and Windows groups. A Windows
login is a member of two Windows groups. These two Windows groups have been
granted access to a sql server.
Login1 Member of Group1 and Group2
Group1 DefaultDbA
Group2 DefaultDbB
How is the default database determined if Login1 is connecting to the sql
server?
Best regards
Ola HallengrenThis is, to the best of my knowledge, not deterministic. There is not setting for this. So, it could
go either way. IMO, an app should never depend on default database - it should set the database in
the connection string. You could, of course, add Login! as a login to SQL Server and specify the
default database for that login (I'm pretty certain that this would be honored).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
> Hello!
> I have a question about default databases and Windows groups. A Windows
> login is a member of two Windows groups. These two Windows groups have been
> granted access to a sql server.
> Login1 Member of Group1 and Group2
> Group1 DefaultDbA
> Group2 DefaultDbB
> How is the default database determined if Login1 is connecting to the sql
> server?
> Best regards
> Ola Hallengren|||Thanks, Tibor. I agree with you that applications should not depend on
default databases. This is not a problem for us. The problem we have is with
the administrators managing the database servers using the SQL Server client
tools.
Enterprise Manager
If the default database is not accessible you can not log on.
Query Analyzer
If the default database is not accessible you can not log on.
Management Studio
There is an option to specify a database in the connection that enables you
to log on, even if the default database is not accessible. However then the
graphical dialogs is not working.
We're considering going away from using default databases (leaving it to
master on all logins).
/Ola
"Tibor Karaszi" wrote:
> This is, to the best of my knowledge, not deterministic. There is not setting for this. So, it could
> go either way. IMO, an app should never depend on default database - it should set the database in
> the connection string. You could, of course, add Login! as a login to SQL Server and specify the
> default database for that login (I'm pretty certain that this would be honored).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
> > Hello!
> >
> > I have a question about default databases and Windows groups. A Windows
> > login is a member of two Windows groups. These two Windows groups have been
> > granted access to a sql server.
> >
> > Login1 Member of Group1 and Group2
> >
> > Group1 DefaultDbA
> > Group2 DefaultDbB
> >
> > How is the default database determined if Login1 is connecting to the sql
> > server?
> >
> > Best regards
> >
> > Ola Hallengren
>|||> We're considering going away from using default databases (leaving it to
> master on all logins).
This is what I do. And I then let the tool specify the database (/d for QA, in the registered server
for SSMS etc.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:5D4CCE2E-E6BD-48DC-832D-517AB35364A4@.microsoft.com...
> Thanks, Tibor. I agree with you that applications should not depend on
> default databases. This is not a problem for us. The problem we have is with
> the administrators managing the database servers using the SQL Server client
> tools.
> Enterprise Manager
> If the default database is not accessible you can not log on.
> Query Analyzer
> If the default database is not accessible you can not log on.
> Management Studio
> There is an option to specify a database in the connection that enables you
> to log on, even if the default database is not accessible. However then the
> graphical dialogs is not working.
> We're considering going away from using default databases (leaving it to
> master on all logins).
> /Ola
>
> "Tibor Karaszi" wrote:
>> This is, to the best of my knowledge, not deterministic. There is not setting for this. So, it
>> could
>> go either way. IMO, an app should never depend on default database - it should set the database
>> in
>> the connection string. You could, of course, add Login! as a login to SQL Server and specify the
>> default database for that login (I'm pretty certain that this would be honored).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
>> news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
>> > Hello!
>> >
>> > I have a question about default databases and Windows groups. A Windows
>> > login is a member of two Windows groups. These two Windows groups have been
>> > granted access to a sql server.
>> >
>> > Login1 Member of Group1 and Group2
>> >
>> > Group1 DefaultDbA
>> > Group2 DefaultDbB
>> >
>> > How is the default database determined if Login1 is connecting to the sql
>> > server?
>> >
>> > Best regards
>> >
>> > Ola Hallengren
>>|||Perfect Match Finder
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
For further details email me at maxonline.sunil@.gmail.com|||> I'm thinking if there is a way to trace if the applications are specifying a
> database on connection, or if they are depending on the default databases.
Seems like Profiler can show you this. If you capture the "User Error Message" event, you will see a
"Changed database context to dbname" events when you start the app. I guess this is the same as if
you do an explicit USE in the code, but whichever the app uses will make the app independent of the
default database for the login.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:6207977A-193E-4FED-832C-E302E37F5BD4@.microsoft.com...
> >IMO, an app should never depend on default database - it should set the
> database in the connection string.
> I'm thinking if there is a way to trace if the applications are specifying a
> database on connection, or if they are depending on the default databases.
> One way is of course to to change the default databases to master and see
> which applications that fails. Is there a more elegant way?
> /Ola Hallengren
>
> "Tibor Karaszi" wrote:
>> > We're considering going away from using default databases (leaving it to
>> > master on all logins).
>> This is what I do. And I then let the tool specify the database (/d for QA, in the registered
>> server
>> for SSMS etc.)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
>> news:5D4CCE2E-E6BD-48DC-832D-517AB35364A4@.microsoft.com...
>> > Thanks, Tibor. I agree with you that applications should not depend on
>> > default databases. This is not a problem for us. The problem we have is with
>> > the administrators managing the database servers using the SQL Server client
>> > tools.
>> >
>> > Enterprise Manager
>> > If the default database is not accessible you can not log on.
>> >
>> > Query Analyzer
>> > If the default database is not accessible you can not log on.
>> >
>> > Management Studio
>> > There is an option to specify a database in the connection that enables you
>> > to log on, even if the default database is not accessible. However then the
>> > graphical dialogs is not working.
>> >
>> > We're considering going away from using default databases (leaving it to
>> > master on all logins).
>> >
>> > /Ola
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> This is, to the best of my knowledge, not deterministic. There is not setting for this. So, it
>> >> could
>> >> go either way. IMO, an app should never depend on default database - it should set the
>> >> database
>> >> in
>> >> the connection string. You could, of course, add Login! as a login to SQL Server and specify
>> >> the
>> >> default database for that login (I'm pretty certain that this would be honored).
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
>> >> news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
>> >> > Hello!
>> >> >
>> >> > I have a question about default databases and Windows groups. A Windows
>> >> > login is a member of two Windows groups. These two Windows groups have been
>> >> > granted access to a sql server.
>> >> >
>> >> > Login1 Member of Group1 and Group2
>> >> >
>> >> > Group1 DefaultDbA
>> >> > Group2 DefaultDbB
>> >> >
>> >> > How is the default database determined if Login1 is connecting to the sql
>> >> > server?
>> >> >
>> >> > Best regards
>> >> >
>> >> > Ola Hallengren
>> >>
>> >>
>>|||I've done some tests with this and unfortunately it doesn't seem to work.
I have a database called [TestDb] and a login called [test]. The login
[test] has been granted access to the database [TestDb]. [TestDb] is also the
default database.
Scenario 1: Logging in using sqlcmd and not specifying a database (depending
on the default database).
sqlcmd -S Server1 -U test -P test
Profiler shows the User Error Message below.
Changed database context to 'TestDb'.
Scenario 2: Logging in using sqlcmd and specifying a database (not depending
on the default database)
sqlcmd -S Server1 -U test -P test -d TestDb
Profiler shows the User Error Message below.
Changed database context to 'TestDb'.
As I see it I can not determine if a database is specified in the connection
or if it is depending on the default database.
/Ola
"Tibor Karaszi" wrote:
> > I'm thinking if there is a way to trace if the applications are specifying a
> > database on connection, or if they are depending on the default databases.
> Seems like Profiler can show you this. If you capture the "User Error Message" event, you will see a
> "Changed database context to dbname" events when you start the app. I guess this is the same as if
> you do an explicit USE in the code, but whichever the app uses will make the app independent of the
> default database for the login.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:6207977A-193E-4FED-832C-E302E37F5BD4@.microsoft.com...
> > >IMO, an app should never depend on default database - it should set the
> > database in the connection string.
> >
> > I'm thinking if there is a way to trace if the applications are specifying a
> > database on connection, or if they are depending on the default databases.
> > One way is of course to to change the default databases to master and see
> > which applications that fails. Is there a more elegant way?
> >
> > /Ola Hallengren
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> > We're considering going away from using default databases (leaving it to
> >> > master on all logins).
> >>
> >> This is what I do. And I then let the tool specify the database (/d for QA, in the registered
> >> server
> >> for SSMS etc.)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> >> news:5D4CCE2E-E6BD-48DC-832D-517AB35364A4@.microsoft.com...
> >> > Thanks, Tibor. I agree with you that applications should not depend on
> >> > default databases. This is not a problem for us. The problem we have is with
> >> > the administrators managing the database servers using the SQL Server client
> >> > tools.
> >> >
> >> > Enterprise Manager
> >> > If the default database is not accessible you can not log on.
> >> >
> >> > Query Analyzer
> >> > If the default database is not accessible you can not log on.
> >> >
> >> > Management Studio
> >> > There is an option to specify a database in the connection that enables you
> >> > to log on, even if the default database is not accessible. However then the
> >> > graphical dialogs is not working.
> >> >
> >> > We're considering going away from using default databases (leaving it to
> >> > master on all logins).
> >> >
> >> > /Ola
> >> >
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> This is, to the best of my knowledge, not deterministic. There is not setting for this. So, it
> >> >> could
> >> >> go either way. IMO, an app should never depend on default database - it should set the
> >> >> database
> >> >> in
> >> >> the connection string. You could, of course, add Login! as a login to SQL Server and specify
> >> >> the
> >> >> default database for that login (I'm pretty certain that this would be honored).
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> >> >> news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
> >> >> > Hello!
> >> >> >
> >> >> > I have a question about default databases and Windows groups. A Windows
> >> >> > login is a member of two Windows groups. These two Windows groups have been
> >> >> > granted access to a sql server.
> >> >> >
> >> >> > Login1 Member of Group1 and Group2
> >> >> >
> >> >> > Group1 DefaultDbA
> >> >> > Group2 DefaultDbB
> >> >> >
> >> >> > How is the default database determined if Login1 is connecting to the sql
> >> >> > server?
> >> >> >
> >> >> > Best regards
> >> >> >
> >> >> > Ola Hallengren
> >> >>
> >> >>
> >>
> >>
>sql

Default Databases and Windows Groups

Hello!
I have a question about default databases and Windows groups. A Windows
login is a member of two Windows groups. These two Windows groups have been
granted access to a sql server.
Login1Member of Group1 and Group2
Group1DefaultDbA
Group2DefaultDbB
How is the default database determined if Login1 is connecting to the sql
server?
Best regards
Ola Hallengren
Thanks, Tibor. I agree with you that applications should not depend on
default databases. This is not a problem for us. The problem we have is with
the administrators managing the database servers using the SQL Server client
tools.
Enterprise Manager
If the default database is not accessible you can not log on.
Query Analyzer
If the default database is not accessible you can not log on.
Management Studio
There is an option to specify a database in the connection that enables you
to log on, even if the default database is not accessible. However then the
graphical dialogs is not working.
We're considering going away from using default databases (leaving it to
master on all logins).
/Ola
"Tibor Karaszi" wrote:

> This is, to the best of my knowledge, not deterministic. There is not setting for this. So, it could
> go either way. IMO, an app should never depend on default database - it should set the database in
> the connection string. You could, of course, add Login! as a login to SQL Server and specify the
> default database for that login (I'm pretty certain that this would be honored).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
>
|||Perfect Match Finder
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
For further details email me at maxonline.sunil@.gmail.com
|||>IMO, an app should never depend on default database - it should set the
database in the connection string.
I'm thinking if there is a way to trace if the applications are specifying a
database on connection, or if they are depending on the default databases.
One way is of course to to change the default databases to master and see
which applications that fails. Is there a more elegant way?
/Ola Hallengren
"Tibor Karaszi" wrote:

> This is what I do. And I then let the tool specify the database (/d for QA, in the registered server
> for SSMS etc.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:5D4CCE2E-E6BD-48DC-832D-517AB35364A4@.microsoft.com...
>

Default Databases and Windows Groups

Hello!
I have a question about default databases and Windows groups. A Windows
login is a member of two Windows groups. These two Windows groups have been
granted access to a sql server.
Login1 Member of Group1 and Group2
Group1 DefaultDbA
Group2 DefaultDbB
How is the default database determined if Login1 is connecting to the sql
server?
Best regards
Ola HallengrenThis is, to the best of my knowledge, not deterministic. There is not settin
g for this. So, it could
go either way. IMO, an app should never depend on default database - it shou
ld set the database in
the connection string. You could, of course, add Login! as a login to SQL Se
rver and specify the
default database for that login (I'm pretty certain that this would be honor
ed).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
> Hello!
> I have a question about default databases and Windows groups. A Windows
> login is a member of two Windows groups. These two Windows groups have bee
n
> granted access to a sql server.
> Login1 Member of Group1 and Group2
> Group1 DefaultDbA
> Group2 DefaultDbB
> How is the default database determined if Login1 is connecting to the sql
> server?
> Best regards
> Ola Hallengren|||Thanks, Tibor. I agree with you that applications should not depend on
default databases. This is not a problem for us. The problem we have is with
the administrators managing the database servers using the SQL Server client
tools.
Enterprise Manager
If the default database is not accessible you can not log on.
Query Analyzer
If the default database is not accessible you can not log on.
Management Studio
There is an option to specify a database in the connection that enables you
to log on, even if the default database is not accessible. However then the
graphical dialogs is not working.
We're considering going away from using default databases (leaving it to
master on all logins).
/Ola
"Tibor Karaszi" wrote:

> This is, to the best of my knowledge, not deterministic. There is not sett
ing for this. So, it could
> go either way. IMO, an app should never depend on default database - it sh
ould set the database in
> the connection string. You could, of course, add Login! as a login to SQL
Server and specify the
> default database for that login (I'm pretty certain that this would be hon
ored).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in messag
e
> news:5A8547EF-89D3-4BE0-BD3F-AD1899F40F0B@.microsoft.com...
>|||> We're considering going away from using default databases (leaving it to
> master on all logins).
This is what I do. And I then let the tool specify the database (/d for QA,
in the registered server
for SSMS etc.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:5D4CCE2E-E6BD-48DC-832D-517AB35364A4@.microsoft.com...[vbcol=seagreen]
> Thanks, Tibor. I agree with you that applications should not depend on
> default databases. This is not a problem for us. The problem we have is wi
th
> the administrators managing the database servers using the SQL Server clie
nt
> tools.
> Enterprise Manager
> If the default database is not accessible you can not log on.
> Query Analyzer
> If the default database is not accessible you can not log on.
> Management Studio
> There is an option to specify a database in the connection that enables yo
u
> to log on, even if the default database is not accessible. However then th
e
> graphical dialogs is not working.
> We're considering going away from using default databases (leaving it to
> master on all logins).
> /Ola
>
> "Tibor Karaszi" wrote:
>|||Perfect Match Finder
http://www.max-online.biz/idevaffil...iate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffil...iate.php?id=804
For further details email me at maxonline.sunil@.gmail.com|||>IMO, an app should never depend on default database - it should set the
database in the connection string.
I'm thinking if there is a way to trace if the applications are specifying a
database on connection, or if they are depending on the default databases.
One way is of course to to change the default databases to master and see
which applications that fails. Is there a more elegant way?
/Ola Hallengren
"Tibor Karaszi" wrote:

> This is what I do. And I then let the tool specify the database (/d for QA
, in the registered server
> for SSMS etc.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in messag
e
> news:5D4CCE2E-E6BD-48DC-832D-517AB35364A4@.microsoft.com...
>

default database problem

I did a stupid thing yesterday: I had to detach a database, and forgot it
was the default for that login account (Windows authentication). After it
was detached, I was unable to connect to that server through enterprise
manager afterward with that login.
Once I realized my error, I found a way to reattach the database using query
analyzer and that worked great. Unfortunately, I still can't login with the
Windows admin account. I connected as a different user (SQL Server internal
account) and saw that the default database for the Windows admin account in
question is now blank. !!!
So it seems I'm in a catch-22. Can't login to fix my login. I can't get a
response from our support staff so I'm trying to figure this out myself. I
need to have this access restored asap. How do I recover from this
embarrassing blunder (technically, not emotionally... lol)?
Thanks,
Randall Arnold
> Once I realized my error, I found a way to reattach the database using query analyzer and that
> worked great.
This indicates that you have some login that you can use, possibly with sysadmin privileges. Use
sp_defaultdb to change the default database for the messed up login.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:1158249818.863918@.xnews001...
>I did a stupid thing yesterday: I had to detach a database, and forgot it was the default for that
>login account (Windows authentication). After it was detached, I was unable to connect to that
>server through enterprise manager afterward with that login.
> Once I realized my error, I found a way to reattach the database using query analyzer and that
> worked great. Unfortunately, I still can't login with the Windows admin account. I connected as
> a different user (SQL Server internal account) and saw that the default database for the Windows
> admin account in question is now blank. !!!
> So it seems I'm in a catch-22. Can't login to fix my login. I can't get a response from our
> support staff so I'm trying to figure this out myself. I need to have this access restored asap.
> How do I recover from this embarrassing blunder (technically, not emotionally... lol)?
> Thanks,
> Randall Arnold
>
|||The problem is, the only login I had with sysadmin privileges is the one I
can no longer use. I tried the same local login I mentioned below, running
sp_defaultdb in query analyzer, and got this error:
Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
Cannot change default database belonging to someone else.
Randall
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23rfuV%23B2GHA.3372@.TK2MSFTNGP04.phx.gbl...
> This indicates that you have some login that you can use, possibly with
> sysadmin privileges. Use sp_defaultdb to change the default database for
> the messed up login.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:1158249818.863918@.xnews001...
>
|||So you tried sp_defaultdb using a login which isn't sysadmin? OK, makes sense it cannot change
default database for somebody else.
If the only sysadmin you have left is the messed up login, use ISQL.EXE, which will allow you in
even though the default database doesn't exist.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:1158254641.735397@.xnews001...
> The problem is, the only login I had with sysadmin privileges is the one I can no longer use. I
> tried the same local login I mentioned below, running sp_defaultdb in query analyzer, and got this
> error:
> Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
> Cannot change default database belonging to someone else.
> Randall
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23rfuV%23B2GHA.3372@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks.
There are other sysadmin logins, I just don't have access to them. I
finally got ahold of our IT guys who do have such access so hopefully they
can get it resolved for me. Thanks for your advice!
Randall
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uyVKisC2GHA.3476@.TK2MSFTNGP04.phx.gbl...
> So you tried sp_defaultdb using a login which isn't sysadmin? OK, makes
> sense it cannot change default database for somebody else.
> If the only sysadmin you have left is the messed up login, use ISQL.EXE,
> which will allow you in even though the default database doesn't exist.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:1158254641.735397@.xnews001...
>
|||Problem fixed by IT support, lesson learned, thanks again!
I gave one of my SQL accounts sysadmin privileges and made sure its default
database was master. ; )
Randall Arnold
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:1158259731.549347@.xnews001...
> Thanks.
> There are other sysadmin logins, I just don't have access to them. I
> finally got ahold of our IT guys who do have such access so hopefully they
> can get it resolved for me. Thanks for your advice!
> Randall
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:uyVKisC2GHA.3476@.TK2MSFTNGP04.phx.gbl...
>

default database problem

I did a stupid thing yesterday: I had to detach a database, and forgot it
was the default for that login account (Windows authentication). After it
was detached, I was unable to connect to that server through enterprise
manager afterward with that login.
Once I realized my error, I found a way to reattach the database using query
analyzer and that worked great. Unfortunately, I still can't login with the
Windows admin account. I connected as a different user (SQL Server internal
account) and saw that the default database for the Windows admin account in
question is now blank. !!!
So it seems I'm in a catch-22. Can't login to fix my login. I can't get a
response from our support staff so I'm trying to figure this out myself. I
need to have this access restored asap. How do I recover from this
embarrassing blunder (technically, not emotionally... lol)?
Thanks,
Randall Arnold> Once I realized my error, I found a way to reattach the database using query analyzer and
that
> worked great.
This indicates that you have some login that you can use, possibly with sysa
dmin privileges. Use
sp_defaultdb to change the default database for the messed up login.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:1158249818.863918@.xnews001...
>I did a stupid thing yesterday: I had to detach a database, and forgot it w
as the default for that
>login account (Windows authentication). After it was detached, I was unabl
e to connect to that
>server through enterprise manager afterward with that login.
> Once I realized my error, I found a way to reattach the database using que
ry analyzer and that
> worked great. Unfortunately, I still can't login with the Windows admin a
ccount. I connected as
> a different user (SQL Server internal account) and saw that the default da
tabase for the Windows
> admin account in question is now blank. !!!
> So it seems I'm in a catch-22. Can't login to fix my login. I can't get
a response from our
> support staff so I'm trying to figure this out myself. I need to have thi
s access restored asap.
> How do I recover from this embarrassing blunder (technically, not emotiona
lly... lol)?
> Thanks,
> Randall Arnold
>|||The problem is, the only login I had with sysadmin privileges is the one I
can no longer use. I tried the same local login I mentioned below, running
sp_defaultdb in query analyzer, and got this error:
Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
Cannot change default database belonging to someone else.
Randall
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23rfuV%23B2GHA.3372@.TK2MSFTNGP04.phx.gbl...
> This indicates that you have some login that you can use, possibly with
> sysadmin privileges. Use sp_defaultdb to change the default database for
> the messed up login.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:1158249818.863918@.xnews001...
>|||So you tried sp_defaultdb using a login which isn't sysadmin? OK, makes sens
e it cannot change
default database for somebody else.
If the only sysadmin you have left is the messed up login, use ISQL.EXE, whi
ch will allow you in
even though the default database doesn't exist.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:1158254641.735397@.xnews001...
> The problem is, the only login I had with sysadmin privileges is the one I
can no longer use. I
> tried the same local login I mentioned below, running sp_defaultdb in quer
y analyzer, and got this
> error:
> Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
> Cannot change default database belonging to someone else.
> Randall
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23rfuV%23B2GHA.3372@.TK2MSFTNGP04.phx.gbl...
>|||Thanks.
There are other sysadmin logins, I just don't have access to them. I
finally got ahold of our IT guys who do have such access so hopefully they
can get it resolved for me. Thanks for your advice!
Randall
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uyVKisC2GHA.3476@.TK2MSFTNGP04.phx.gbl...
> So you tried sp_defaultdb using a login which isn't sysadmin? OK, makes
> sense it cannot change default database for somebody else.
> If the only sysadmin you have left is the messed up login, use ISQL.EXE,
> which will allow you in even though the default database doesn't exist.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:1158254641.735397@.xnews001...
>|||Problem fixed by IT support, lesson learned, thanks again!
I gave one of my SQL accounts sysadmin privileges and made sure its default
database was master. ; )
Randall Arnold
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:1158259731.549347@.xnews001...
> Thanks.
> There are other sysadmin logins, I just don't have access to them. I
> finally got ahold of our IT guys who do have such access so hopefully they
> can get it resolved for me. Thanks for your advice!
> Randall
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:uyVKisC2GHA.3476@.TK2MSFTNGP04.phx.gbl...
>

default database problem

I did a stupid thing yesterday: I had to detach a database, and forgot it
was the default for that login account (Windows authentication). After it
was detached, I was unable to connect to that server through enterprise
manager afterward with that login.
Once I realized my error, I found a way to reattach the database using query
analyzer and that worked great. Unfortunately, I still can't login with the
Windows admin account. I connected as a different user (SQL Server internal
account) and saw that the default database for the Windows admin account in
question is now blank. !!!
So it seems I'm in a catch-22. Can't login to fix my login. I can't get a
response from our support staff so I'm trying to figure this out myself. I
need to have this access restored asap. How do I recover from this
embarrassing blunder (technically, not emotionally... lol)?
Thanks,
Randall Arnold> Once I realized my error, I found a way to reattach the database using query analyzer and that
> worked great.
This indicates that you have some login that you can use, possibly with sysadmin privileges. Use
sp_defaultdb to change the default database for the messed up login.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:1158249818.863918@.xnews001...
>I did a stupid thing yesterday: I had to detach a database, and forgot it was the default for that
>login account (Windows authentication). After it was detached, I was unable to connect to that
>server through enterprise manager afterward with that login.
> Once I realized my error, I found a way to reattach the database using query analyzer and that
> worked great. Unfortunately, I still can't login with the Windows admin account. I connected as
> a different user (SQL Server internal account) and saw that the default database for the Windows
> admin account in question is now blank. !!!
> So it seems I'm in a catch-22. Can't login to fix my login. I can't get a response from our
> support staff so I'm trying to figure this out myself. I need to have this access restored asap.
> How do I recover from this embarrassing blunder (technically, not emotionally... lol)?
> Thanks,
> Randall Arnold
>|||The problem is, the only login I had with sysadmin privileges is the one I
can no longer use. I tried the same local login I mentioned below, running
sp_defaultdb in query analyzer, and got this error:
Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
Cannot change default database belonging to someone else.
Randall
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23rfuV%23B2GHA.3372@.TK2MSFTNGP04.phx.gbl...
>> Once I realized my error, I found a way to reattach the database using
>> query analyzer and that worked great.
> This indicates that you have some login that you can use, possibly with
> sysadmin privileges. Use sp_defaultdb to change the default database for
> the messed up login.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:1158249818.863918@.xnews001...
>>I did a stupid thing yesterday: I had to detach a database, and forgot it
>>was the default for that login account (Windows authentication). After it
>>was detached, I was unable to connect to that server through enterprise
>>manager afterward with that login.
>> Once I realized my error, I found a way to reattach the database using
>> query analyzer and that worked great. Unfortunately, I still can't login
>> with the Windows admin account. I connected as a different user (SQL
>> Server internal account) and saw that the default database for the
>> Windows admin account in question is now blank. !!!
>> So it seems I'm in a catch-22. Can't login to fix my login. I can't get
>> a response from our support staff so I'm trying to figure this out
>> myself. I need to have this access restored asap. How do I recover from
>> this embarrassing blunder (technically, not emotionally... lol)?
>> Thanks,
>> Randall Arnold
>|||So you tried sp_defaultdb using a login which isn't sysadmin? OK, makes sense it cannot change
default database for somebody else.
If the only sysadmin you have left is the messed up login, use ISQL.EXE, which will allow you in
even though the default database doesn't exist.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:1158254641.735397@.xnews001...
> The problem is, the only login I had with sysadmin privileges is the one I can no longer use. I
> tried the same local login I mentioned below, running sp_defaultdb in query analyzer, and got this
> error:
> Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
> Cannot change default database belonging to someone else.
> Randall
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23rfuV%23B2GHA.3372@.TK2MSFTNGP04.phx.gbl...
>> Once I realized my error, I found a way to reattach the database using query analyzer and that
>> worked great.
>> This indicates that you have some login that you can use, possibly with sysadmin privileges. Use
>> sp_defaultdb to change the default database for the messed up login.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
>> news:1158249818.863918@.xnews001...
>>I did a stupid thing yesterday: I had to detach a database, and forgot it was the default for
>>that login account (Windows authentication). After it was detached, I was unable to connect to
>>that server through enterprise manager afterward with that login.
>> Once I realized my error, I found a way to reattach the database using query analyzer and that
>> worked great. Unfortunately, I still can't login with the Windows admin account. I connected
>> as a different user (SQL Server internal account) and saw that the default database for the
>> Windows admin account in question is now blank. !!!
>> So it seems I'm in a catch-22. Can't login to fix my login. I can't get a response from our
>> support staff so I'm trying to figure this out myself. I need to have this access restored
>> asap. How do I recover from this embarrassing blunder (technically, not emotionally... lol)?
>> Thanks,
>> Randall Arnold
>>
>|||Thanks.
There are other sysadmin logins, I just don't have access to them. I
finally got ahold of our IT guys who do have such access so hopefully they
can get it resolved for me. Thanks for your advice!
Randall
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uyVKisC2GHA.3476@.TK2MSFTNGP04.phx.gbl...
> So you tried sp_defaultdb using a login which isn't sysadmin? OK, makes
> sense it cannot change default database for somebody else.
> If the only sysadmin you have left is the messed up login, use ISQL.EXE,
> which will allow you in even though the default database doesn't exist.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:1158254641.735397@.xnews001...
>> The problem is, the only login I had with sysadmin privileges is the one
>> I can no longer use. I tried the same local login I mentioned below,
>> running sp_defaultdb in query analyzer, and got this error:
>> Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
>> Cannot change default database belonging to someone else.
>> Randall
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23rfuV%23B2GHA.3372@.TK2MSFTNGP04.phx.gbl...
>> Once I realized my error, I found a way to reattach the database using
>> query analyzer and that worked great.
>> This indicates that you have some login that you can use, possibly with
>> sysadmin privileges. Use sp_defaultdb to change the default database for
>> the messed up login.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
>> news:1158249818.863918@.xnews001...
>>I did a stupid thing yesterday: I had to detach a database, and forgot
>>it was the default for that login account (Windows authentication).
>>After it was detached, I was unable to connect to that server through
>>enterprise manager afterward with that login.
>> Once I realized my error, I found a way to reattach the database using
>> query analyzer and that worked great. Unfortunately, I still can't
>> login with the Windows admin account. I connected as a different user
>> (SQL Server internal account) and saw that the default database for the
>> Windows admin account in question is now blank. !!!
>> So it seems I'm in a catch-22. Can't login to fix my login. I can't
>> get a response from our support staff so I'm trying to figure this out
>> myself. I need to have this access restored asap. How do I recover
>> from this embarrassing blunder (technically, not emotionally... lol)?
>> Thanks,
>> Randall Arnold
>>
>>
>|||Problem fixed by IT support, lesson learned, thanks again!
I gave one of my SQL accounts sysadmin privileges and made sure its default
database was master. ; )
Randall Arnold
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:1158259731.549347@.xnews001...
> Thanks.
> There are other sysadmin logins, I just don't have access to them. I
> finally got ahold of our IT guys who do have such access so hopefully they
> can get it resolved for me. Thanks for your advice!
> Randall
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:uyVKisC2GHA.3476@.TK2MSFTNGP04.phx.gbl...
>> So you tried sp_defaultdb using a login which isn't sysadmin? OK, makes
>> sense it cannot change default database for somebody else.
>> If the only sysadmin you have left is the messed up login, use ISQL.EXE,
>> which will allow you in even though the default database doesn't exist.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
>> news:1158254641.735397@.xnews001...
>> The problem is, the only login I had with sysadmin privileges is the one
>> I can no longer use. I tried the same local login I mentioned below,
>> running sp_defaultdb in query analyzer, and got this error:
>> Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
>> Cannot change default database belonging to someone else.
>> Randall
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23rfuV%23B2GHA.3372@.TK2MSFTNGP04.phx.gbl...
>> Once I realized my error, I found a way to reattach the database using
>> query analyzer and that worked great.
>> This indicates that you have some login that you can use, possibly with
>> sysadmin privileges. Use sp_defaultdb to change the default database
>> for the messed up login.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
>> news:1158249818.863918@.xnews001...
>>I did a stupid thing yesterday: I had to detach a database, and forgot
>>it was the default for that login account (Windows authentication).
>>After it was detached, I was unable to connect to that server through
>>enterprise manager afterward with that login.
>> Once I realized my error, I found a way to reattach the database using
>> query analyzer and that worked great. Unfortunately, I still can't
>> login with the Windows admin account. I connected as a different user
>> (SQL Server internal account) and saw that the default database for
>> the Windows admin account in question is now blank. !!!
>> So it seems I'm in a catch-22. Can't login to fix my login. I can't
>> get a response from our support staff so I'm trying to figure this out
>> myself. I need to have this access restored asap. How do I recover
>> from this embarrassing blunder (technically, not emotionally... lol)?
>> Thanks,
>> Randall Arnold
>>
>>
>

Default Database brought offline SQL Server Agent Job fails!!!

Hi All,

There is this SQL Server agent job that was calling a SSIS package (uses windows authentication) which was executing fine till the default database for the user was brought ofline, and now the job fails citing authentication failure for the user as the reason.

I have tried pointing master as the Default database for the user and now able to connect to SSMS using the users authentication, but the SQL Agent job wont succeed.

Any pointers ?

Have you tried tracing the connection attempt using SQL Profiler?

This is a SQL engine security issue - its nothing to do with SSIS.

-Jamie

|||

Jamie,

You were right this has nothing to do with SSIS indded, I was using a Proxy account and I tried resetting the password, and it seems to work now. Thanks in helping me look at the right area.

debugging SQL stored procedure

Hi

I have a simple windows application which uses two stored procedures at the backend to fetch the data and display it in the grid of UI

I wanted to debug these stored procedures line by line like using run time storage dump for various variable and statements used in the procedures as we do it in Visual studio using functional keys etc.

or

Any way to do this using SQL server 2000 query analyzer? The stored procedures contains nearly five hundred lines of code in sql.

Early reply is much appreciated.

Thanks!

In query analyzer, show object browser. then select your stored proc, and right click.

Select debug.

For more, look at this article http://www.15seconds.com/Issue/050106.htm

|||

Thanks for your help. This debug option is not available in sql2k5. I do not see any debug option available for sql2k5 when I right click the stored proc. Is there any option to do in sql2k5?

Thanks!

Santhosh

debugging SQL stored procedure

Hi

I have a simple windows application which uses two stored procedures at the backend to fetch the data and display it in the grid of UI

I wanted to debug these stored procedures line by line like using run time storage dump for various variable and statements used in the procedures as we do it in Visual studio using functional keys etc.

or

Any way to do this using SQL server 2000 query analyzer? The stored procedures contains nearly five hundred lines of code in sql.

Early reply is much appreciated.

Thanks!

In query analyzer, show object browser. then select your stored proc, and right click.

Select debug.

For more, look at this article http://www.15seconds.com/Issue/050106.htm

|||

Thanks for your help. This debug option is not available in sql2k5. I do not see any debug option available for sql2k5 when I right click the stored proc. Is there any option to do in sql2k5?

Thanks!

Santhosh

debugging sp sql 2000

Hi All.
I'm running SQL Server 2000 sp3 over a Windows 2003 Server, and my client
machine is a XP Pro sp2, with SQL Clients sp3.
I cannot debug stored procedure from my client machine and i recieve this
error:
Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on servername (Error = 0x80070005). Ensure that client-side components, such
as SQLDBREG.EXE, are installed and registered on PC_CECCATO. Debugging
disabled for connection 89.
does anyone have a solution?
Many thanks.
StefanoSee "Troubleshooting the Transact-SQL Debugger" in BOL. Also, see if this
helps.
The T-SQL Debugger is turned off by default for earlier clients after you
install SQL Server 2000 Service Pack 3
http://support.microsoft.com/defaul...kb;en-us;328151
AMB
"stefano ceccato" wrote:

> Hi All.
> I'm running SQL Server 2000 sp3 over a Windows 2003 Server, and my client
> machine is a XP Pro sp2, with SQL Clients sp3.
> I cannot debug stored procedure from my client machine and i recieve this
> error:
> Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on servername (Error = 0x80070005). Ensure that client-side components, su
ch
> as SQLDBREG.EXE, are installed and registered on PC_CECCATO. Debugging
> disabled for connection 89.
> does anyone have a solution?
> Many thanks.
> Stefano
>
>|||Thanks for your message, but i already tried all the workarouds published in
MSDN...
i remember that with the same server i can debug if the client machine in a
windows 2000 professional... maybe is a problem of my Windows XP sp2
machine!
nothing about this'
regards
stefano
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EEF974A8-85F7-4512-AA13-27D0E8860A51@.microsoft.com...
> See "Troubleshooting the Transact-SQL Debugger" in BOL. Also, see if this
> helps.
> The T-SQL Debugger is turned off by default for earlier clients after you
> install SQL Server 2000 Service Pack 3
> http://support.microsoft.com/defaul...kb;en-us;328151
>
> AMB
> "stefano ceccato" wrote:
>

Debugging in SSIS - Immediate and Command windows

Hi everyone,
I am having a difficult time debugging a package that I'm working on. I read in BOL that the immediate window should be an option during debugging but I can't find it anywhere(nor can I enable it), and I was planning on using it to access an oledb source property that is using an expression. Is the command window the same as immediate? I didn't orignally think so but I'm not sure. What is the syntax to use for this once I find it?
Thanks,
Adrian

You can't access runtime values of an oledb source property but you can see what data going downstream using visualizers.

Command window isn't supported.

Can you give me a ref where in BOL you found about immediate window?

|||Nick, thanks for responding, I didn't see that anyone had responed until now.
I found it here:
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/54a458cc-9f4f-4b48-8cf2-db2e0fa7756c.htm

Adrian

Debug SQL User Functions

Does anyone know if there's a way to debug T-SQL user functions using sql
server 2000 sp3 on Windows Small Business Server 2003 from a client computer
running an Access 2003 mdb front-end on Windows XP SP2?
JayNot directly. You could debug the code if you create it as a procedure inste
ad.
ML|||ML,
I was afraid that would be the answer. The reason I wrote it as a function
is so that I could put it in a server-side query as in: "SELECT
MyFunc(SomeField, @.SomeInputVariable) AS SomeLabel FROM dbo.SomeTable;". Is
there a way to do the same thing with a procedure?
Jay
"ML" wrote:

> Not directly. You could debug the code if you create it as a procedure ins
tead.
>
> ML|||All you need in such a case is a sample of input parameters - for instance i
n
a table variable - and then you execute the code you intend to use in the
function with each set of parameters in a loop.
Or maybe you can post your DDL and get a 'second opinion'.
ML|||Try this out:
declare @.output2 varchar(30)
exec testbysandeep 1,2,@.output1=@.output2 OUTPUT
select @.output2
output1 is the field which is the output of the function. You assign this
value to the output2 variable.Then by doing a select statement you display
the value.
Hope this helps.
jains
"jay" wrote:
> ML,
> I was afraid that would be the answer. The reason I wrote it as a functi
on
> is so that I could put it in a server-side query as in: "SELECT
> MyFunc(SomeField, @.SomeInputVariable) AS SomeLabel FROM dbo.SomeTable;". I
s
> there a way to do the same thing with a procedure?
> Jay
> "ML" wrote:
>|||Hi Jay,
You can debug a UDF from QA by making a small SP that calls the UDF. The QA
debugger steps into the UDF with F11 the same as VS.
Cheers
Doug Forster
"jay" <jay@.discussions.microsoft.com> wrote in message
news:E2A1224F-03FA-4422-BD86-215675844400@.microsoft.com...
> ML,
> I was afraid that would be the answer. The reason I wrote it as a
> function
> is so that I could put it in a server-side query as in: "SELECT
> MyFunc(SomeField, @.SomeInputVariable) AS SomeLabel FROM dbo.SomeTable;".
> Is
> there a way to do the same thing with a procedure?
> Jay
> "ML" wrote:
>|||Hi Doug Forster,
Is there any kind of configuration setting involed at server side ?
With warm regards
Jatinder|||Well I do this ON the server with admin rights and it just works. Maybe
someone else knows if it is possible to debug from another box, though I
notice the docs caution against debugging on a production server.
Cheers
Doug Forster
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1122878941.648594.17860@.g47g2000cwa.googlegroups.com...
> Hi Doug Forster,
> Is there any kind of configuration setting involed at server side ?
> With warm regards
> Jatinder
>|||Hi Forster ,
The problem is that it is not working (debuggin) even on Server .
It says that you are logged as 'Local Account' . Do I have to Logon the
service as administrator woul that effect other clients?
With warm regards
Jatinder Singh
Doug Forster wrote:
> Well I do this ON the server with admin rights and it just works. Maybe
> someone else knows if it is possible to debug from another box, though I
> notice the docs caution against debugging on a production server.
> Cheers
> Doug Forster
> "jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
> news:1122878941.648594.17860@.g47g2000cwa.googlegroups.com...|||Hi I just want to add what I am facing when trying to debug a procedure
(created on master DB using sa login)
when I start debugging on this procedure in query analyzer, I recieve a
msg box which says:
SP debugging may not work properly if you log on as 'Local System
account'
while SQL Server is configured to run as a service.
You can open Event Viwer to see details.
Do you want to continue?
When I continue with this and execute this procedure by providing
parameter values, I get the print outputs, but procedure execution
doesn't break on break points!!
I am puzzled now how to work around with this? How can I break
execution on break points?
Thanks

debug memory dumps

So can one use WinDbg to debug SQL dumps and also the Windows OS related
memory.dmp files ?
If so, is there a good introductory tutorial on how to setup and use it ? I
dont intend to be a pro here, but just experiment and do some first level of
diagnosis
Thanks
There are a few items you can glean from the dumps but mostly you have to
send it into support. The symbols are not public. Check out Bob Ward's
chapter in "SQL Server 2005 Practical Troubleshooting" or his PASS session
recording if you have access.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23N0yEocPIHA.1184@.TK2MSFTNGP04.phx.gbl...
> So can one use WinDbg to debug SQL dumps and also the Windows OS related
> memory.dmp files ?
> If so, is there a good introductory tutorial on how to setup and use it ?
> I dont intend to be a pro here, but just experiment and do some first
> level of diagnosis
> Thanks
>

debug memory dumps

So can one use WinDbg to debug SQL dumps and also the Windows OS related
memory.dmp files ?
If so, is there a good introductory tutorial on how to setup and use it ? I
dont intend to be a pro here, but just experiment and do some first level of
diagnosis
ThanksThere are a few items you can glean from the dumps but mostly you have to
send it into support. The symbols are not public. Check out Bob Ward's
chapter in "SQL Server 2005 Practical Troubleshooting" or his PASS session
recording if you have access.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23N0yEocPIHA.1184@.TK2MSFTNGP04.phx.gbl...
> So can one use WinDbg to debug SQL dumps and also the Windows OS related
> memory.dmp files ?
> If so, is there a good introductory tutorial on how to setup and use it ?
> I dont intend to be a pro here, but just experiment and do some first
> level of diagnosis
> Thanks
>

debug memory dumps

So can one use WinDbg to debug SQL dumps and also the Windows OS related
memory.dmp files ?
If so, is there a good introductory tutorial on how to setup and use it ? I
dont intend to be a pro here, but just experiment and do some first level of
diagnosis
ThanksThere are a few items you can glean from the dumps but mostly you have to
send it into support. The symbols are not public. Check out Bob Ward's
chapter in "SQL Server 2005 Practical Troubleshooting" or his PASS session
recording if you have access.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23N0yEocPIHA.1184@.TK2MSFTNGP04.phx.gbl...
> So can one use WinDbg to debug SQL dumps and also the Windows OS related
> memory.dmp files ?
> If so, is there a good introductory tutorial on how to setup and use it ?
> I dont intend to be a pro here, but just experiment and do some first
> level of diagnosis
> Thanks
>

Debug in QA Quit after SP2

Three development team members connect to SQL server 2000 on a 2003 server
within a windows domain. Used QA to debug stored procedures for several
months. Two of the three have installed XP SP2 on local machine. Now,
debug stops working on all three and receive the following message.
************************************************** *******
Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on CSTSMAIN (Error = 0x80070005). Ensure that client-side components, such
as SQLDBREG.EXE, are installed and registered on CJAD502KR41. Debugging
disabled for connection 67.
************************************************** **********
Regards,
Michael Mach
no fix but check below
http://support.microsoft.com/default.aspx?kbid=839280
Andy.
"Michael Mach" <Michael.Mach@.cmaaccess.com> wrote in message
news:e8pTgahiEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Three development team members connect to SQL server 2000 on a 2003 server
> within a windows domain. Used QA to debug stored procedures for several
> months. Two of the three have installed XP SP2 on local machine. Now,
> debug stops working on all three and receive the following message.
> ************************************************** *******
> Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to
debugger
> on CSTSMAIN (Error = 0x80070005). Ensure that client-side components, such
> as SQLDBREG.EXE, are installed and registered on CJAD502KR41. Debugging
> disabled for connection 67.
> ************************************************** **********
>
> Regards,
> Michael Mach
>
|||This is a known issue with WinXP SP2. A fix for this has shipped in hotfix
944 on 6/2/04. It shipped in 32-bit only originally. Any later hotfix for
32-bit or 64-bit also includes this due to the cumulative nature of our
hotfixes.
We will be updating the following KB to reflect this:
BUG: SQL debugging does not work in Visual Studio .NET after you install
ID: 839280
To get a hotfix you need to contact PSS.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Mach" <Michael.Mach@.cmaaccess.com> wrote in message
news:e8pTgahiEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Three development team members connect to SQL server 2000 on a 2003 server
> within a windows domain. Used QA to debug stored procedures for several
> months. Two of the three have installed XP SP2 on local machine. Now,
> debug stops working on all three and receive the following message.
> ************************************************** *******
> Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to
debugger
> on CSTSMAIN (Error = 0x80070005). Ensure that client-side components, such
> as SQLDBREG.EXE, are installed and registered on CJAD502KR41. Debugging
> disabled for connection 67.
> ************************************************** **********
>
> Regards,
> Michael Mach
>
|||Euan,
I plead ignorant. How does one contact PSS?
--Scott
slbrook at hotmail.com
"Euan Garden[MS]" <euang@.online.microsoft.com> wrote in message
news:%23vw%230L8jEHA.704@.TK2MSFTNGP12.phx.gbl...
> This is a known issue with WinXP SP2. A fix for this has shipped in hotfix
> 944 on 6/2/04. It shipped in 32-bit only originally. Any later hotfix for
> 32-bit or 64-bit also includes this due to the cumulative nature of our
> hotfixes.
> We will be updating the following KB to reflect this:
> BUG: SQL debugging does not work in Visual Studio .NET after you install
> ID: 839280
> To get a hotfix you need to contact PSS.
> --
> -Euan
> Please reply only to the newsgroup so that others can benefit. When
> posting,
> please state the version of SQL Server being used and the error
> number/exact
> error message text received, if any.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Michael Mach" <Michael.Mach@.cmaaccess.com> wrote in message
> news:e8pTgahiEHA.1652@.TK2MSFTNGP09.phx.gbl...
> debugger
>
|||Nevermind.
I found it.
Thanks for the tip.
--scott
"Scott Brookhart" <slbrook@.hotmail.com> wrote in message
news:uJcqlPSkEHA.2848@.TK2MSFTNGP15.phx.gbl...
> Euan,
> I plead ignorant. How does one contact PSS?
> --Scott
> slbrook at hotmail.com
> "Euan Garden[MS]" <euang@.online.microsoft.com> wrote in message
> news:%23vw%230L8jEHA.704@.TK2MSFTNGP12.phx.gbl...
>