Sql-server – SQL logins access

sql serversql-server-2012ssms

I am trying to set up a set of SQL logins (one for each developer) on a server who can have access to a given set of databases, currently the setup is as follows:

Login 1: db_owner of databases 1, 2, 3 and 4

Login 2: db_owner of database 5 (this is because this login is used by someone else who shouldn't have access to the other databases).

Is there a way to add a set of new logins that would have access to all the mentioned databases without changing the existing logins? As far as I know you can't have multiple db_owner's of a database.

(I know that using sa would do the job, but the databases are actually hosted on a third parties server with other customer's databases, so we can't use that!)

Best Answer

As per this question

want to be able to access it and change anything in it within reason (Tables, Procs, Views, Schemas etc.)

You can create a login using below acript and map it to user and then grant necessary privileges to user. Granting privilege to SQL server user is complex topic and you must read this article and this for understanding it.

SQL Server, requires ALTER ANY LOGIN permission on the server or membership in the securityadmin fixed server role to create login.

-- Creates the login with password
CREATE LOGIN login_name 
    WITH PASSWORD = 'Password@1234';
GO

-- Creates a database user for the login created above.
CREATE USER Login_name FOR LOGIN Login_name;
GO

EXEC sp_addrolemember N'db_datareader', N'Login_name'--here login_name is user name

EXEC sp_addrolemember N'db_datawriter', N'Login_name'--here login_name is user name

EXEC sp_addrolemember N'db_ddladmin', N'Login_name'--here login_name is user name
Related Question