TSQL to Map User to Database

Question

So I'm not able to user enterprise manager to do this... If I was I wouldn't even be asking this question. So I'm wondering if there is a way through TSQL to execute a command that maps a User to a particular Database and grants them 'owner' permissions.

Thanks...

Answer 1

CREATE USER and sp_addrolemember

Answer 2

Officially, you want to create a database user that is mapped to a login. To do that, you would use:

Create User <username> For LOGIN <loginname>

This obviously requires that the login exist. After that you would then call:

exec sp_addrolemember 'db_owner', <username>

This presumes that the account with which you are connecting to the database has privileges to add members to the db_owner role.

Answer 3

Change default database of a login:

alter login <loginname> with default_database = <dbname>;

Create an user in a database for a given login:

use <dbanme>;
create user <username> from login <loginname>;

Make an user member of db_owner group:

use <dbname>
sp_addrolemember 'db_owner', '<username>';

Make a login 'dbo' of a database:

alter authorization on database::<dbanme> to <loginname>;

Answer 4

USE [YourDB]
GO
CREATE USER [xyx] FOR LOGIN [xyz]
GO
ALTER USER [xyz] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_owner', N'xyz'
GO

Answer 5

Of course typically, you don't want to grant owner permissions to users. This is what those of us in the DBA world call "A bad thing."