Granting database access

Question

Granting database access

Brian Brown-204626

Hall of Fame

Points: 3211
More actions
September 17, 2012 at 1:41 pm

#264292

When I use the GRANT CONNECT statement to grant database access, it only works with a Windows login.
USE database
GRANT CONNECT TO [domain\login]
If I use it with a SQL Server login, I get the following message:
USE database
GRANT CONNECT TO [login]
Msg 15151, Level 16, State 1, Line 1
Cannot find the user 'login', because it does not exist or you do not have permission.
Does anyone know why there would be a difference?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

i think it's because of two things:

when you create a SQL user, that implicitly grants the CONNECT permissions; logins from the domain already exist, so it makes sense you need to explicitly grant them permissions to connect.

when you tried the GRANT CONNECT TO [login],

there was no SQL login that already existed right? I would expect that if the string was not found in either the domain or the locally created logins, you'd get that error.

now if you did CREATE LOGIN [login]; you would see it inherits connect permissions , and no need to explcitly grant CONNECT i believe.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Brian Brown-204626 Hall of Fame Points: 3211 More actions · Answer 2

The SQL Server login already exists, but I need to grant access to a database from a T-SQL command, rather than use Management Studio.

Lowell SSC Guru Points: 323504 More actions · Answer 3

Brian Brown-204626 (9/18/2012)
The SQL Server login already exists, but I need to grant access to a database from a T-SQL command, rather than use Management Studio.

granting access to a database is done by creating a USER mapped to the login; then the user is placed in roles (or granted access directly to teh user, but that's not the best practice)

access to objects isn't given to a login, but instead to the USER that is tied tot eh login.

example:

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ClarkKent' AND type = 'S') --'S' = SQL login

BEGIN

--create our user

CREATE LOGIN [ClarkKent]

WITH PASSWORD=N'NotTheRealPassword',

DEFAULT_DATABASE=[master],

CHECK_EXPIRATION=ON,

CHECK_POLICY=ON

--Make sure I'm in the correct database!

USE Dev;

GO

--create the Role for my Dev guys/role which runs DDL scripts

CREATE ROLE [Dev_Admins]

ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [Dev_Admins]

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [Dev_Admins]

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [Dev_Admins]

GRANT EXECUTE,ALTER TO [Dev_Admins]

--create role for my normal users

CREATE ROLE [DevUsers]

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [DevUsers]

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [DevUsers]

GRANT EXECUTE TO [DevUsers]

--create my user for the login

CREATE USER [ClarkKent] FOR LOGIN [ClarkKent]--make sure I'm in the correct database, and create some roles

--Add Clark to my normal user role:

EXEC sp_addrolemember N'DevUsers', N'ClarkKent'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Brian Brown-204626 Hall of Fame Points: 3211 More actions · Answer 4

Brian Brown-204626

Hall of Fame

Points: 3211

September 18, 2012 at 8:26 am

#1539122

Thank you. CREATE USER was what I was forgetting.