How do I set up a new Login with restricted Access?

Question

Post reply

How do I set up a new Login with restricted Access?

M Jones

SSCommitted

Points: 1711
More actions
January 27, 2009 at 8:31 am

#129831

I'm not officially a DBA but in our small company, I have been picked to be such. I need to set up in SQLServer 2005 a new login for a user that we only wish to have use two new databases. In these databases, we want to give the user full control to add/update/delete data and table/view definitions. We do not want this user to have access to the other databases on the server.
How do I set this up?
Thanks!
~mj

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 1

Hi there,

Assuming that the two databases already exits and that the user is a domain user (DOMAIN\USERNAME), this will work:

USE [master]
CREATE LOGIN [{YOUR-DOMAIN}\{user}] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
USE [{db-name-1}]
CREATE USER [{YOUR-DOMAIN}\{user}] FOR LOGIN [{YOUR-DOMAIN}\{user}]
EXEC sp_addrolemember N'db_owner', N'{YOUR-DOMAIN}\{user}'
USE [{db-name-2}]
CREATE USER [{YOUR-DOMAIN}\{user}] FOR LOGIN [{YOUR-DOMAIN}\{user}]
EXEC sp_addrolemember N'db_owner', N'{YOUR-DOMAIN}\{user}'

This script creates an user from AD which has DBO (database owner) rights to two databases. Beyond that the user has now SQL server rights at all. Replace anything inside {} with the real names.

Adam Zacks-------------------------------------------Be Nice, Or Leave

M Jones SSCommitted Points: 1711 More actions · Answer 2

The user is not a domain user. It is a generic user found only in SQLServer so connection will require SQL Authentication. This way we can give this user login to more than one user and only maintain the one user in SQLServer....

I think I got it to work now (I had checked sysadmin for a server role and did not want this. The only server role assigned now is public and then when mapped to each DB via UserMappings, I have checked db_datareader, db_datawriter, db_owner and public roles.)

~mj

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 3

Sounds like your about there. Gave you the script to be easier (if your new to this, less to explain), wasnt being patronising.

One thing though, if a user has DBO you dont need to specify anything else (the other rights are inherited), so you may as well remove all db rights other then DBO (for no other reason then its tidier and best practive).

😉

Adam Zacks-------------------------------------------Be Nice, Or Leave

noeld SSC Guru Points: 96590 More actions · Answer 4

you may want to change "db_owner" to "db_ddladmin" 😉

* Noel

M Jones SSCommitted Points: 1711 More actions · Answer 5

Thanks for the info!

One question tho - if I change the db_owner to db_ddladmin, what will this do?

~mj

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

Members of db_ddladmin can run DDL commands in the database so they can create objects (tables, views, stored procedures). It's a bit more restrictive than db_owner which can do just about anything in the database. See "database roles" in Books OnLine for details.

Greg