TSQL to Map User to Database

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 开发者_Python百科a command that maps a User to a particular Database and grants them 'owner' permissions.

Thanks...

Change default database of a login:

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

Create a user in a database for a given login:

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

Make a user member of db_owner group:

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

Make a login 'dbo' of a database:

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

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.

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

CREATE USER and sp_addrolemember

My complete solution for SQL. You can customize it according your needs:

   #region SQL-SERVER-FUNCTION
    /// <summary>
    /// Assign User in to specific role for SQL server database
    /// </summary>
    /// <param name="Server">Server address - example: yourServer.cloudapp.net,1433</param>         
    /// <param name="UserID">User name with sysadmin role</param>
    /// <param name="Database">User database</param>
    /// <param name="Role">New role for this user</param>
    /// <param name="UserToRole">User assigned to new role</param>   
    public static void AddUserToRoles(string Server, string UserID, string Password, string Database, string Role, string UserToRole)
    {               
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = "SERVER = " + Server + "; DATABASE = " + Database + " ; User ID = " + UserID + "; Pwd = " + Password;             
        string sqlCreateDBQuery = " EXEC sp_addrolemember '" + Role + "', " + UserToRole; 
        SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, connection);
        try
        {
            connection.Open();
            myCommand.ExecuteNonQuery();
        }
        catch{}//(System.Exception ex)
        finally
        {
            connection.Close();
        }
        return;
    }

    /// <summary>
    /// Create new User in specified database base on Login in SQL server
    /// </summary>
    /// <param name="Server">Server address - example: yourServer.cloudapp.net,1433</param>         
    /// <param name="UserID">User name with sysadmin role</param>
    /// <param name="Database">Database for created user</param>
    /// <param name="NewUser">New user Name</param>
    /// <param name="FromLogin">Create user base on this SQL server login</param>   
    public static void CreateUserInDatabase(string Server, string UserID, string Password, string Database, string NewUser, string FromLogin)
    {
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = "SERVER = " + Server + "; DATABASE = "+ Database + " ; User ID = " + UserID + "; Pwd = " + Password;
        string sqlCreateDBQuery = "CREATE USER " + NewUser + " FROM LOGIN " + FromLogin;        
        SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, connection);
        try
        {
            connection.Open();
            myCommand.ExecuteNonQuery();
        }
        catch { }//(System.Exception ex)
        finally
        {
            connection.Close();
        }
        return;
    }

    /// <summary>
    /// Create new Login in SQL server
    /// </summary>
    /// <param name="Server">Server address - example: yourServer.cloudapp.net,1433</param>         
    /// <param name="UserID">User name with sysadmin role</param>
    /// <param name="Password">Sysadmin user password</param>
    /// <param name="NewLoginName">New Login Name</param>
    /// <param name="NewLoginPassword">Password for new Login</param>              
    public static void CreateLoginInSqlServer(string Server, string UserID, string Password, string NewLoginName, string NewLoginPassword)
    {
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = "SERVER = " + Server + "; DATABASE = master; User ID = " + UserID + "; Pwd = " + Password;
        string sqlCreateDBQuery = "CREATE LOGIN [" + NewLoginName + "] WITH PASSWORD='"+ NewLoginPassword +"' " +               
            ", CHECK_POLICY=OFF, DEFAULT_DATABASE=db_crs_0002, DEFAULT_LANGUAGE=[English];";         
        SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, connection);
        try
        {
            connection.Open();
            myCommand.ExecuteNonQuery();
        }
        catch { }//(System.Exception ex)
        finally
        {
            connection.Close();
        }
        return;
    }


    /// <summary>
    /// Create new empty database on SQL server base on model database stored in SQL server
    /// </summary>
    /// <param name="Server">Server address - example: yourServer.cloudapp.net,1433</param>
    /// <param name="NewDatabaseName">Name for new database</param>
    /// <param name="UserID">User name with sysadmin role</param>
    /// <param name="Password">Sysadmin user password</param>
    public static void CreateNewDatabaseSqlserver(string Server, string NewDatabaseName, string UserID, string Password)                 
    {
        string sqlCreateDBQuery = " CREATE DATABASE " + NewDatabaseName;
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = "SERVER = " + Server + "; DATABASE = master; User ID = " + UserID + "; Pwd = " + Password;  
        SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, connection);
        try
        {
            connection.Open();
            myCommand.ExecuteNonQuery();  
        }
        catch { }//(System.Exception ex)
        finally
        {
            connection.Close();
        }
        return;
    }
    #endregion 

using: Create on SQL server Login with sysadmin permissions: AdminAPI

        SQLserverAPI.CreateNewDatabaseSqlserver("yourServer.cloudapp.net,1433", "databaseName", "AdminAPI", "password*");
        SQLserverAPI.CreateLoginInSqlServer("yourServer.cloudapp.net,1433", "AdminAPI", "password*", "My_Login", "password*");
        SQLserverAPI.CreateUserInDatabase("yourServer.cloudapp.net,1433", "AdminAPI", "password*", "databaseName", "User1", "My_Login");
        SQLserverAPI.AddUserToRoles("yourServer.cloudapp.net,1433", "AdminAPI", "password*", "databaseName", "db_datareader", "User1");
        SQLserverAPI.AddUserToRoles("yourServer.cloudapp.net,1433", "AdminAPI", "password*", "databaseName", "db_datawriter", "User1");
        SQLserverAPI.AddUserToRoles("yourServer.cloudapp.net,1433", "AdminAPI", "password*", "databaseName", "db_owner", "User1");

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."