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?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  September 17, 2012 at 2:32 pm

  #1538814

  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

  September 18, 2012 at 8:05 am

  #1539113

  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: 323522

  More actions

  September 18, 2012 at 8:24 am

  #1539120

  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

  September 18, 2012 at 8:26 am

  #1539122

  Thank you. CREATE USER was what I was forgetting.