CODEWITHSUNDEEP
sql-serverwindows-authentication
mark1234
mark1234

Reputation: 1121

SQL Server - Giving Access to an SP to an AD user

Using SQLServer 2008 R2 and converting excel macros (vba) from SQL authentication to AD authentication. I have an AD Group called Company\Excel_Commission and and harry is a member of that group.

I can't even connect to SQL server in SSMS when logged in as CompanyA\Harry and using SQL authentication.

I added a new user CompanyA\Excel_Commission under Logins. Server Role is public

if I do "script user as create to" I get:

CREATE LOGIN [CompanyA\Excel_Commission] FROM WINDOWS WITH DEFAULT_DATABASE=[Commission], DEFAULT_LANGUAGE=[us_english]

I also added the user to the DB where the SP is and granted execute permission. The user is also a member of the roles db_datareader and db_datawriter in this DB:

Again if I run "script user as create to", I get

CREATE USER [Excel_Commission] FOR LOGIN [CompanyA\Excel_Commission]

Further info.

Ran this

SELECT endpnt.name,
       suser_name(perms.grantee_principal_id) as grantee_principal,
       perms.permission_name, perms.state_desc
    FROM
       sys.server_permissions perms,
       sys.endpoints endpnt
    WHERE
       perms.class = 105
       AND perms.major_id = endpnt.endpoint_id

Got this

name    grantee_principal   permission_name state_desc
TSQL Local Machine  public  CONNECT GRANT
TSQL Named Pipes    public  CONNECT GRANT
TSQL Default TCP    public  CONNECT GRANT
TSQL Default VIA    public  CONNECT GRANT

Error is Error: 18456, Severity: 14, State: 11.

Which seems to be "Valid login but server access failure"

What am i missing?

Regards

mark

Upvotes: 0

Views: 392

Answers (1)

benjamin moskovits
benjamin moskovits

Reputation: 5458

Connecting to SQL Server is a two step dance.

Step One is the creation of a login (which can be a Windows user/group) which users Windows passwords among other things or a SQL Server Login in which you create a user name and password (and has to be enabled). You typically assign a default database (should not be master) to the new login.

Step 2 is you use the login created in step one to create a user in one, or more, databases and grant permissions to the user to do things. Sometimes by putting the user into a SQL Server group you can grant automatic permission to that user. Try to grant as narrow permissions as possible.

Upvotes: 1

Related Questions