General discussion

Locked

SQL Server and VB6: Stored Procedures

By ebartholom ·
We recently implemented a new SQL Server 7.0.

I am developing an interface using VB6. Prior to the new server implementation, we had a test server. On the test server, I created stored procedures and was able to execute them from my VB interface (I used a data environment for a connection). I was the dbo of this database and therefore "dbo" was listed as "Owner" of the stored procedures created.

We are using NT Authentication

On the new server, the Network administrator created an NT group called PM_DBO and assigned me to that group. Now when I create a stored proc, the "Owner" is Domain\MyNTusername. I can execute the stored proc within the SQL Enterprise manager, but when I attempt to create a command oject/execute the stored proc from VB, I get an access or syntax error.

My guess is that there should not be an NT group created for PM_DBO and that my login alone should be given rights as DBO of the database. If this is so, if the dbo needs to change in the future, can my login be dropped and another person added as dbo?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL Server and VB6: Stored Procedures

by pVp In reply to SQL Server and VB6: Store ...

Yes, the db ownership can be changed by running

sp_changedbowner [@loginame =] 'login' [,[@map =] remap_alias_flag]

...must be run be member of sysadmin role or current owner

Similarly, the owner of the sp change be changed by

sp_changeobjectowner [@objname =] 'object', [@newowner =] 'owner'

...must be run members of the db_owner fixed database role, or a member of both the db_ddladmin and db_securityadmin fixed database roles

Collapse -

SQL Server and VB6: Stored Procedures

by ebartholom In reply to SQL Server and VB6: Store ...

The question was auto-closed by TechRepublic

Collapse -

SQL Server and VB6: Stored Procedures

by chrandrob In reply to SQL Server and VB6: Store ...

Also, as a general rule of thumb, I always add dbo. to my stored procedure names. Like this:

Create Stored Procedure dbo.MyProc AS


This will name the sp MyProc and make dbo the owner regardless of who creates it. Unless you want an sp to be executable by you only, you should make the dbo the owner.

Collapse -

SQL Server and VB6: Stored Procedures

by ebartholom In reply to SQL Server and VB6: Store ...

The question was auto-closed by TechRepublic

Collapse -

SQL Server and VB6: Stored Procedures

by ebartholom In reply to SQL Server and VB6: Store ...

This question was auto closed due to inactivity

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums