October 27, 2003 at 3:37 pm
I have an accounting application that has hundreds of users. I would like to create a stored proc that adds new domain accounts to SQL and to the database in specific roles, based on job title, whenever someone new is hired. I don't want to add objects like tables and procs to the accounting database and have created a separate database called "Integration" for any objects that I need for this and other tasks.
What I am trying to do is to add users using dynamic sql from my Integration database to the accounting database. Whenever I build a statment that looks like this:
use database
go
sp_adduser 'mydomain\newUser','newUser','myRole'
I get "Incorrect syntax near 'go'". If I leave out the go line, I get "Incorrect syntax near 'sp_adduser'". I print the statements before executing them, so if I copy the statment and paste into the query window, it executes fine.
The only way around this that I see is to run the proc from the accounting database instead of integration. That way I don't have to use the "use database" statement. I really hate to do this, because I should not be adding stored procs to the accounting database.
Any ideas?
Thanks,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 27, 2003 at 4:18 pm
I would use this method:
-- Allow this NT user to login to server...
exec sp_grantlogin 'mydomain\newUser'
-- Grant access to that db...
exec accounting..sp_grantdbaccess 'mydomain\newUser','newUser'
-- Attach role to user...
exec accounting..sp_addrolemember 'myRole', 'newUser'
-Dan
-Dan
October 28, 2003 at 5:18 am
You could use dynamic sql. Either build up the string 'use database;blah blah' and then use sp_executesql to run it, or just call your code as is from an ADO connection.
Andy
October 28, 2003 at 7:08 am
To piggy-back on Andy...
The GO is a batch separator, it's not an actual T-SQL command. Query Analyzer uses it to realize what T-SQL statements to group together. That's why you're getting the incorrect syntax error with GO.
If a stored procedure call is not the first command in a batch, you must use EXEC. Since you don't have EXEC, that's why you're getting the incorrect syntax error without GO.
As far as database..sproc, this isn't going to work because those stored procedures exist in the master database.
In keeping with what Andy has already said:
USE database;
EXEC sp_adduser 'mydomain\newUser', 'newUser', 'myRole'
That should work.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
October 28, 2003 at 8:06 am
Getting rid of the "go" and adding the "exec" worked.
Thanks for your help!
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply