SQL server 2005 - user rights

Question

I have created one user named "tuser" with create database rights in SQL server 2005. and given the 'db_owner' database role of master and msdb database to "tuser".

From this user login when I run the script for create database then it will create new database. But "tuser" don't have access that newly created database generated from script.

Any one have any idea?, I want to write the script so "tuser" have access that new created database after creation and can have add user permission of newly created database.

I want to give 'db_owner' database roles to "tuser" on that newly created database in the same script which create new database. The script run under 'tuser'.

Answer 1

Grant securityadmin server role to [tuser]

Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.

Answer 2

CREATE DATABASE says

Each database has an owner that can perform special activities in the database. The owner is the user that creates the database. The database owner can be changed by using sp_changedbowner (Transact-SQL).

So tuser should own the DB already.

However, you could set up tuser as db_owner in the model db which used as the template for every db creation

BTW, why make tuser the owner of master and msdb?

Answer 3

If tuser doesn't have access to the new database it means is not the owner. The database owner cannot be denied access into his/her own database.

• How does the CREATE DATABASE statement look like? Do you have any AUTHORIZATION clause that would change the database ownership of the new database?
• Who is the actual owner of the new database? Check SELECT name, SUSER_SNAME(owner_sid) FROM sys.databases;

Answer 4

Hi all,

Thanks for your input.

i have given access of tuser to database from ehich the new database created. now the issue is resolved.

Many thanks.