Problmes with user-account-query

[Guest_imported]

I have made a stored procedure (am using SQL Server7) like:

CREATE PROCEDURE AddUser AS
GO
USE Database1
GO
sp_grantdbaccess 'Albino'
GO
USE Database2
GO
sp_grantdbaccess 'Albino'
GO

But when I run this from Query Analyzer and I open the stored procedure in enterprise manager, it only looks like:

CREATE PROCEDURE AddUser AS

that's all. And it tries to apply the access to the databases when I run it from query analyzer. I wonder how I could make a stored procedure that gives the user Albino access to those databases when I run the stored procedure, like:

EXECUTE AddUser

Thanks for your time!

-Bob

 

[cdukes]

This is because you have the GO keyword after the create
procdure as. The Go signifies the end of the current executable command, and thus the end of the stored procedure.

Remove all the GO commands as follows and add execute commands to run the other SP's.

CREATE PROCEDURE AddUser AS

USE Database1

EXECUTE sp_grantdbaccess 'Albino'

USE Database2

EXECUTE sp_grantdbaccess 'Albino'

Hope this helps,

Chris Dukes

 

[Guest_imported]

Thanks a lot! I will try this and it seems to work.

-Bob