creating a user in TSQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

creating a user in TSQL

Author

Topic

Julian Winstanley
Starting Member

2 Posts

Posted - 2001-11-05 : 10:09:59

Hi

I'm using a piece of tsql writtend by a coleague which grants all rights to the nominated user in the currently selected database (when run in query analyzer - sql server 7). I'm trying to modify the code to create the user as well.

I can create a user, using "sp_addlogin", but I've hit a problem. I need to create the login ion the master database, but run the rest of the script against a different database. I either need to be abe to pass is the database name as a variable to the "use statement (e.g. use @dbname), or inlude the use statement ina single exec call as suggested by the msdn documentation (e.g. Exec ("use master") sp_addlogin etc. etc.)

Any suggestions as to how I can solve this one? Each time. SQL Query Analyzer rejects my code, and since it doesn't comile it won't run

TIA

---Actual code I'm tyring to use----
DECLARE @resId nvarchar(600)
DECLARE @sql nvarchar(600)
DECLARE @username nvarchar(50)
DECLARE @password nvarchar(50)
DECLARE @dbname nvarchar(50)

--put your username here.
SET @username = 'newuser'

--put your password here
SET @password = 'passwrod'

--put your databasebname here
SET @dbname = 'db1'

--if user doesn't exist, create it on the master database
IF (SELECT COUNT(*) AS USERCOUNT FROM master.dbo.sysxlogins WHERE NAME = @username) = 0
begin
exec (use master) sp_addlogin @username , @password,@dbname
end

--add access rights to current database
exec sp_grantdbaccess @username

--Grant privileges to stored procedures
DECLARE resCursor CURSOR FOR
SELECT name FROM sysobjects WHERE xtype = 'P'
OPEN resCursor

FETCH NEXT FROM resCursor INTO @resId

WHILE @@FETCH_STATUS = 0 BEGIN
set @sql = 'grant all on ' + @resId + ' to ' + @username
execute(@sql)
FETCH NEXT FROM resCursor INTO @resId
END
CLOSE resCursor
DEALLOCATE resCursor

--grant privileges to tables
DECLARE resCursor CURSOR FOR
SELECT name FROM sysobjects WHERE xtype = 'X'
OPEN resCursor

FETCH NEXT FROM resCursor INTO @resId

WHILE @@FETCH_STATUS = 0 BEGIN
set @sql = 'grant all on ' + @resId + ' to ' + @username
execute(@sql)
FETCH NEXT FROM resCursor INTO @resId
END
CLOSE resCursor
DEALLOCATE resCursor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources