Here’s how you can GRANT or REVOKE permissions to Endpoints. First; we’ll start off by creating two SQL accounts. One which will own the endpoint ( by creating it ), and the other which will be used by the applications to connect to the Endoint.
Here’s a quick tip. Whenever the CONNECT permission is granted to an Endpoint on any account it will appear as the ‘owner’ of the Endpoint granted it. So if you were looking at the catalog views sys.server_permissions, and the sys.server_principals you’ll see the grantor_principal_id column under the sys.server_permissions view will map back to the principal_id column under the sys.server_principals view. This is the GRANTOR.
There really isn’t anything special about the Endpoint owner account. I just called it “EndpointOwner” so I know it’s purpose. This can be a Windows account, SQL account, or perhaps even a group, but I’m keeping it simple with a straight-up SQL login.
create login [EndpointOwner] with password=’MyComplexPassword’, default_database=[master], default_language=[us_english], check_expiration=off, check_policy=off; alter server role [sysadmin] add member [EndpointOwner]
We are also going to create an SQL account that the application will use to connect using the Endpoint. We are calling it the EndpointUser. This is the same process as above.
create login [EndpointUser] with password=’MyComplexPassword’, default_database=[master], default_language=[us_english], check_expiration=off, check_policy=off;
You’ll then login to Management Studio using the EndpointOwner account, and create (or drop and recreate) your Endpoint if it already exists. This will of course set the Endpoint Owner to your new EndpointOwner SQL login. You could of course run the statement GRANT TAKE OWNERSHIP ON ENDPOINT and simply specify your new owner account name. I could show you the statement to create the Endpoint, but this is more about managing the permissions with GRANT or REVOKE rather than creating the Endpoint it’s self.
use master; grant take ownership on endpoint::MyEndpoint to MyEndpointOwnerAccount with grant option;
Next we’ll be granting the CONNECT permission to the Endpoint. In this example we’ll be using the Endpoint ‘ConfigMgrEndpoint’. By the way; you’ll need the CONTROL permission on the endpoint or ALTER ANY ENDPOINT permission just to run the GRANT statement against an Endpoint. If you’re the DBA; you’ll usually have the rights to perform this action, but if you don’t, this is just something to keep in mind in case you run into trouble running grants against other accounts specifically for Endpoints.
use [master]; grant connect on endpoint::[ConfigMgrEndpoint] to [EndpointUser];
Alternatively; you could use the GUI to GRANT CONNECTION permissions to the Endpoint on a particular user, but the process as you can see below is riddled with different windows, and prompts, and makes for a complicated process. In the example below we are using the EndpointOwner as the user that the applications will use to connect to SQL Server through the Endpoint.
1. Go to your database instance.
2. Expand Security
3. Expand Logins
4. Double click the user your application will use to connect to the Endpoint. Again in our example we are using the EndpointOwner account.
5. Select ‘Securables’ on the left, and ‘Search’ on the right.
6. Select ‘Specific Objects’, and click ‘OK’.
7. Select ‘Object Types’.
8. Check ‘Endpoints’ and click ‘OK’.
9. Click ‘Browse’.
10. Select the Endpoint you are granting the CONNECT too, and click ‘OK’. In this example we are using Endpoint [ConfigMgrEndpoint].
11. Click ‘OK’.
12. Select the Endpoint form the list, and check ‘Grant’, and click ‘OK’.
Suppose you want to REVOKE the CONNECT permissions to the ENDPOINT… Here’s a quick statement to do that instead of using the GUI.
Removing CONNECT permissions is much easier than adding cause you don’t have to search the different objects. You just need to double click the account in SQL Server, go to Securables, and simply uncheck the GRANT. Here is what you will see when using the GUI to remove the CONNECT permission.
Hope this helpful.
Categories: SQL Stuff