Sql-server – Correct Selection of SQL server instance db owner login name

sql serverwindows

I am attempting to connect RSA Archer (5.2) to an instance of SQL server 2008 r2 on WS 2008 R2.
When Archer attempts it's initial connection I get –
The ConnectString property has not been initialized. Please ensure that all databases on the server have a dbowner.

That instance I was connecting to did indeed have a db owner and it had
a DBO role too. I suspect it is the login account that I am associating to this DB owner user ID.

Does anyone have any suggestions how I can get around this problem?

Thanks in advance.

Best Answer

Not sure about your specific case, but I would check to see if the login you're using is a member of the dbo role in the database you're trying to connect to. You can query role membership using this query:

use YourDatabase;

select 
    r.name role_name,
    u.name db_login,
    u.type_desc
from (select * from sys.database_principals where type = 'R') r
    join sys.database_role_members rm on (r.principal_id = rm.role_principal_id)
    join (select * from sys.database_principals where type != 'R') u on rm.member_principal_id = u.principal_id
where r.name = 'db_owner'
order by r.principal_id,u.type_desc,u.name;

If you need to add the login to the DBO role:

Use YourDatabase;
exec sp_addrolemember 'db_owner','FOO\bar';
Related Question