Re: Please Help

From: Bob Barrows (reb_01501@yahoo.com)
Date: 01/14/03 

From: "Bob Barrows" <reb_01501@yahoo.com>
Date: Tue, 14 Jan 2003 07:08:16 -0500

oj,
Remember, he wants to do this in a stored procedure, thus preventing the use
of the keyword "use". It sounds as if he could use this code in a stored
procedure created in the Master database ... let's see ... yes, that works.

David, create a procedure in Master using oj's code - something like this:
use master
go
create procedure usp_AddUser (
 @role sysname, @user sysname)
 AS
--select @role='db_datareader',@user='abc'

--create new sql login
exec sp_addlogin @user,@user
--grant access to pubs db
exec pubs..sp_adduser @user
--add to pubs datareader role
exec pubs..sp_addrolemember @role,@user
--grant access to test db
exec test..sp_adduser @user
--add to test datareader role
exec test..sp_addrolemember @role,@user

Then execute it in your other stored procedure: exec master..usp_AddUser
<role>,<name>

Thanks,
oj

"oj" <nospam_ojngo@home.com> wrote in message
news:uZ2AMI4uCHA.2288@TK2MSFTNGP09...
> here is how to do it without dynamic sql...
>
> e.g.
> use master
> go
> declare @role sysname, @user sysname
> select @role='db_datareader',@user='abc'
>
> --create new sql login
> exec sp_addlogin @user,@user
> --grant access to pubs db
> exec pubs..sp_adduser @user
> --add to pubs datareader role
> exec pubs..sp_addrolemember @role,@user
> --see if the security is set
> exec pubs..sp_helpuser @user
>
> --
> -oj
> http://www.rac4sql.net
>
>
> "David Lucus" <davidl@studentlife.tamu.edu> wrote in message
> news:etA5uu0uCHA.2512@TK2MSFTNGP11...
> > I have a stored procedure that adds new sql server accounts from the
> > web.
> > there are 2 databases that need teh login. The database where the
> > stored
> > procedure resides has no problems granting roles and db access
> permissions,
> > however when i try to give it permissions to the other database nothing
> > is
> > happening. It is not adding a user to the roles or throwing an error.
> here
> > is the code i am using to add the user to the second db
> >
> > EXECUTE DBName.dbo.sp_grantdbaccess @newname
> >
> > EXECUTE DBName.dbo.sp_addrolemember @sturolename, @newname
> >
> > where @newname is the variable with the name, and @sturolename is the
> > role
> > name.
> >
> > if anyone has any ideas please help.
> >
> > thx,
> > David
> >
> >
>
>

Relevant Pages

  • Re: noise words, @@ERROR, and stop and resume indexing
    ... EXEC sp_fulltext_catalog 'adsfull', 'stop' ... > 1) check noise words inside stored procedure ... > can be solved by changing the language specific file with noise words ... ...
    (microsoft.public.sqlserver.fulltext)
  • source text file as input parameter from a stored procedure
    ... I am using an active X script in combination with a global ... transform task that loads data from a .txt file to a table. ... I can also tell that the stored procedure is correctly passing in the ... EXEC sp_OAGeterrorinfo @object, @hr ...
    (microsoft.public.sqlserver.dts)
  • Re: Repeating 3 Datapump actions between the same datasource/destinati
    ... How to loop through a global variable Rowset ... > exec p_PullDataForDates1 20050101,20050131 ... > task that configure the SourceSQLStatement for the 2 pumps (to fill in the ... > blanks for the stored procedure parameter). ...
    (microsoft.public.sqlserver.dts)
  • Re: Stored Procedure Fails Only When Called By Agent
    ... Stored Procedure 1: ... EXEC sp_addlinkedserver '10.1.2.10', 'SQL Server' ... LOCALMACHINE\myaccount/pass3, the same account that owns the job. ...
    (microsoft.public.sqlserver.replication)
  • temporary table problem
    ... I am building an internet appliacation. ... Many users share only one sql login. ... During page generation there is on stored procedure, ... Thank you Tom ...
    (microsoft.public.sqlserver.programming)