Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Granting database access Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 1:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:12 AM
Points: 137, Visits: 297
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?
Post #1360435
Posted Monday, September 17, 2012 2:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1360446
Posted Tuesday, September 18, 2012 8:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:12 AM
Points: 137, Visits: 297
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.
Post #1360808
Posted Tuesday, September 18, 2012 8:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1360817
Posted Tuesday, September 18, 2012 8:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:12 AM
Points: 137, Visits: 297
Thank you. CREATE USER was what I was forgetting.
Post #1360819
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse