Re: Little database user account script need help...

Upps it does work . Nice

DECLARE @db_name VARCHAR(50),@sp_addrolemember VARCHAR(50),@login_name
VARCHAR(50)

SET @db_name='dbtemp'

SET @login_name='randd'

SELECT @sp_addrolemember = quotename(@db_name) + '.sys.sp_addrolemember'

EXEC @sp_addrolemember db_owner, @login_name







"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:%23hotxzlkIHA.5956@xxxxxxxxxxxxxxxxxxxxxxx
Hmmm, no success
SELECT @sp_addrolemember = quotename(@db_name) + '.sys.sp_addrolemember'

EXEC @sp_addrolemember db_owner, @login_name

/*

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@db_name".

Msg 137, Level 15, State 2, Line 2

Must declare the scalar variable "@sp_addrolemember".

*/

DECLARE @db_name VARCHAR(50),@sp_addrolemember VARCHAR(50),@login_name
VARCHAR(50)

SELECT @sp_addrolemember = quotename(@db_name) + '.sys.sp_addrolemember'

EXEC @sp_addrolemember db_owner, @login_name

/*

Could not find stored procedure ''.

*/





"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9A7185E154445Yazorman@xxxxxxxxxxxx
Uri Dimant (urid@xxxxxxxxxxx) writes:
Erland
SELECT @sp_addrolemember = quotename(@db_name) +
'.sys.sp_addrolemember
EXEC @sp_addrolemember db_owner, @login_name

How does this script work? I cannot run it on SS2005 sp2

There is a single quote missing on the first line.

The tricks are two:

1) EXEC @var runs the procedure in @var
2) EXEC some_db..sp_systemproc, runs that system procedure in the context
of
the some_db database.

What happened when you tried the above?
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx




.

Relevant Pages

  • Re: Update databases
    ... Compares if all tables in one database have analog in second ... declare @sqlStr varchar ... exec ('declare @Name sysname select @Name=name from ... -- ##CompareStr - will be used to pass comparing strings into dynamic script ...
    (microsoft.public.sqlserver.programming)
  • =?Utf-8?Q?Re:_XML_and_Pound_=28=C2=A3=29_Symbol?=
    ... To be honest we were looking at moving away from XML anyway (too clunky and ... DECLARE @m VARCHAR ... exec sp_xml_preparedocument @h OUTPUT, @m ... for the string you are passing into SQL Server. ...
    (microsoft.public.sqlserver.xml)
  • Re: IN(@variable) clause and Table Data Type variable
    ... > separated character values all enclosed in pairs of single quotes. ... > also need to use a Table data type variable which raises error when EXEC ... > command is run. ... > -- declare and set Table variable ...
    (microsoft.public.sqlserver.programming)
  • Re: In(@variable) clause and TABLE datatype variable
    ... > separated character values all enclosed in pairs of single quotes. ... > also need to use a Table data type variable which raises error when EXEC ... > command is run. ... > -- declare and set Table variable ...
    (microsoft.public.sqlserver.programming)
  • Re: Disable Replication, remove rowguide-column?
    ... exec sp_configure N'allow updates', 1 ... DECLARE @username varchar ... FETCH NEXT FROM list_triggers INTO @name, ... create table syssubscriptions (artid int, srvid smallint, dest_db sysname, ...
    (microsoft.public.sqlserver.replication)