create user for sql server

Question

The code below allow me to create a user in sql server through the use of a query. it currently creates a user profile but it gives the user access to all of the databases on the server. I would only like them to have Access to the "TEST" database how would i go about doing this

CREATE LOGIN [SupportUser]
WITH PASSWORD=N'SupportUser',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

You need to do it with sql command line? If not just use the maangement studio where you can set the user roles. — YvesR, Mar 06 '13 at 16:30
I am already setting the roles but only want the user to access that one datbase — user2108195, Mar 06 '13 at 16:33
I think if you want that user to have access to only and only TEST database then DEFAULT_DATABASE should also be TEST not MASTER. — JackLock, Mar 06 '13 at 16:36
Your script creates a login, not a user, and unless you added it to the `sysadmin` role or another server role then by default it has no access to any user databases. So I think your question is really "how do I grant a login access to a specific database"? See [this question](http://stackoverflow.com/questions/6688880/how-do-i-grant-read-access-for-a-user-to-a-database-in-sql-server). — Pondlife, Mar 06 '13 at 17:40

JackLock · Answer 1 · 2013-03-06T16:39:29.177

1

CREATE LOGIN [SupportUser] WITH PASSWORD=N'SupportUser', DEFAULT_DATABASE=[MASTER], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TEST]
GO
CREATE USER [SupportUser] FOR LOGIN [SupportUser]
GO
USE [TEST]
GO
EXEC sp_addrolemember N'db_datareader', N'SupportUser'
GO
USE [TEST]
GO
EXEC sp_addrolemember N'db_datawriter', N'SupportUser'
GO
USE [TEST]
GO
EXEC sp_addrolemember N'db_ddladmin', N'SupportUser'
GO

edited Mar 06 '13 at 16:39

answered Mar 06 '13 at 16:31

JackLock

1,168
1
13
26

What schema this use gets when you use this? Default dbo. ? – YvesR Mar 06 '13 at 16:32
1

Yes. Since question doesn't mention specifics about what level of access to database is required, I am assuming that user will have READ, WRITE and Execute permissions. – JackLock Mar 06 '13 at 16:34

score 0 · Answer 2 · answered Mar 06 '13 at 16:31

Try this.

CREATE LOGIN [SupportUser] 
WITH PASSWORD=N'SupportUser', 
DEFAULT_DATABASE=[master], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF
GO
USE [TEST]
GO
CREATE USER [SupportUser] FOR LOGIN [SupportUser]
GO
use [TEST]
GO
GRANT SELECT ON [dbo].[myView] TO [SupportUser]
GO

create user for sql server

2 Answers2