db_owner role - 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 2005 Forums

Transact-SQL (2005)

db_owner role

Author

Topic

Mast3rMind
Starting Member

3 Posts

Posted - 2007-01-16 : 13:05:04

Hi!

I'm using SQL Server 2005 with C++. I need to set the role "db_owner" to a user, but I don't know how to do this.

I tried:

USE myDB;
sp_addrolemember 'db_owner','myUser'

But I got the following message:

The procedure 'sys.sp_addrolemember' cannot be executed within a transaction.

I also tried the command CREATE ROLE, but it didn't work... I think I'm missing something.

Anyone can help me?

Thx in advance

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-16 : 13:06:56

Exactly what the message says, you must execute that instruction outside of any transaction. In you C++ code you must be creating or opening a transaction before you execute that command. Don't do that.

Mast3rMind
Starting Member

3 Posts

Posted - 2007-01-16 : 13:23:41

Hum, sorry but what do you mean by "transaction"?

I use SQLOLEDB as the provider. Here is my code:


sprintf_s(szBuffer,nBufferSize,
"CREATE LOGIN %s WITH PASSWORD = '%s', CHECK_POLICY = OFF, DEFAULT_DATABASE = %s",
"myUser",
"myPwdd",
"myDB");
pCon->Execute((_bstr_t)szBuffer, NULL, adCmdText & adExecuteNoRecords);

sprintf_s(szBuffer,nBufferSize,
"USE %s",
"myDB");
pCon->Execute((_bstr_t)szBuffer, NULL, adCmdText & adExecuteNoRecords);

sprintf_s(szBuffer,nBufferSize,
"CREATE USER %s FOR LOGIN %s",
"myUser",
"myLogin");
pCon->Execute((_bstr_t)szBuffer, NULL, adCmdText & adExecuteNoRecords);

sprintf_s(szBuffer,nBufferSize,
"USE %s",
"myDB");
pCon->Execute((_bstr_t)szBuffer, NULL, adCmdText & adExecuteNoRecords);

sprintf_s(szBuffer,nBufferSize,
"sp_addrolemember 'db_owner','%s'",
"myUser");
pCon->Execute((_bstr_t)szBuffer, NULL, adCmdText & adExecuteNoRecords);

Thx for your kind help.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-16 : 16:07:38

The transaction is associated with the connection, so I cannot tell from the code you gave, you'll have to show everything where the pCon variable is created.

Mast3rMind
Starting Member

3 Posts

Posted - 2007-01-17 : 09:51:26

Thank you for your help!

I resolved the problem by adding a "COMMIT" before the "sp_addrolemember" command. I think that by doing that, I closed my 'user-defined transaction'.

Thx again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources