CODEWITHSUNDEEP
sql-server
Russ960
Russ960

Reputation: 1339

How do I test for the CONNECT permission on SQL Server?

I have some database accounts that were created with a script but for some reason on some systems it lacks the connect permission and are disabled. Once applied it works. I want to check for this on systems for the future. How can I check the user has the CONNECT permission? Thank you.

Upvotes: 0

Views: 2353

Answers (1)

Russ960
Russ960

Reputation: 1339

With the help of @SQLSoldier on Twitter I found the answer:

SELECT *
FROM sys.database_principals dprinc
    INNER JOIN sys.database_permissions dperm
    ON dprinc.principal_id = dperm.grantee_principal_id
WHERE dprinc.name = '<username>' AND dperm.permission_name = 'CONNECT'

Upvotes: 3

Related Questions