- Don’t use impersonation: Unfortunately, some people think that the recommendation to use Windows authentication means that in things like SSRS, SSAS, Tableau, IIS, etc means that you have to use impersonation. I mean, what security minded person doesn’t like the sound of “Windows integrated security”. Sounds secure, right?
Where it falls down is that it requires end users to have permissions in the database directly. The argument I usually hear is that users don’t know they have permissions to access the database so no harm done. Users have a way of finding out, and when they find out, they start logging in and running their own ad hoc queries that aren’t optimized or use indexes.
I’ve seen it happen over and over again. A user complains that a value on the report looks wrong, so the developer sends them a query to prove that it matches the value in the database. The user logs in and runs the query, and it starts. They run other queries. They tell their co-workers how to validate the report data on their own. Performance tanks.
You absolutely should be using Windows authentication, but you should use a service account (non-user domain account) that the app or data source uses and then control user permissions at the app level.
- Don’t forget to drop the user when you drop a login: When you drop a login, any users in the databases that are mapped to it remain unless you explicitly drop them too. Well that means that all of their permissions are intact and the user gets those permissions if they are able to log in to the server, even if the access is through a different account. For example if someone has a user in a database and their login gets deleted, but they still have login access through a group membership that maps to a different database, they get all perms granted to them. Even different accounts in different databases.
You can test it yourself. Add a user to 2 Windows security groups (call them SampleDev and SampleTest). Gave SampleDev read access to Database1 and SampleTest read access to Database2. Drop the login for SampleTest but not the user in the database (just like in real life). Then have the user log in and try to read from both databases. Even though they can only login as a member of SampleDev, their access in Database2 still works because the user is still there.
- Check to see what access paths a user has: This goes hand-in-hand with the one above. How are you supposed to know if a given user has access through another path, like a security group, in SQL Server? There is a built-in system extended stored procedure to check that: xp_logininfo.
This extended stored procedure takes 2 parameters, the account to check and the text string “all” or “members”. For this case, we are talking about the all option. The all option shows you all login paths for the specified account. Generally, you pass in a user’s account to see what all groups it belongs to that have access.
1Exec sys.xp_logininfo N'domain\user', 'all';The results returned are 1 record per login path. The last column, permission_path, is the group or user through which they have access to login.
- See who is in your groups: Since we are already talking about xp_logininfo, lets talk about the other choice for option number 2, “members”. You may have guessed that this returns all members of a group login. If so, you are correct. Pass it a Windows group that is a login in SQL Server and the members option to see all the members of the group that have access through it.
- Make sure your databases have a valid owner: If you create a database or restore a new database, you are set as the owner of the database. The reason for this is that if I give you permissions to create a database, it is assumed you will need to be able to do stuff with that database. So the creator is made the owner. Not a member of the db_owner group in the database, but the actual database owner listed in sys.databases. If you look up the owner of a database you created, the owner_sid column will display your SID (security Identifier). Your SID will be automatically mapped to the system database user “dbo”. You know the one you cannot alter even as a sysadmin?
So what do you think happens if the person who owns the database leaves the company and their SID is no longer valid? You can get weird permissions errors. And by you, I mean the DBA. The sysadmin. The dbo account (database owner) bypasses permissions checking within the database. So what’s the easiest way to make sure that a sysadmin has full perms within a database? Within the context of the database, sysadmins impersonate dbo. If the dbo account is not valid, you get an error saying that the action cannot be completed in the current security context. Of course not, you’re impersonating a ghost. The person no longer exists at the company.
I used to use the following query to find users that did not have valid owners:
1234Select D.name As DBName, P.name As OwnerNameFrom sys.databases As DInner Join sys.server_principals As POn P.sid = D.owner_sid;If you guessed that I said “used to use” because it has a big flaw, you’re right. When your login rights comes from a group membership, it doesn’t use the group’s SID, it still uses yours. So there may not be a matching login in sys.server_principals for all valid owners. Then I discovered the built-in function suser_sname(). It accepts a SID and returns the Windows user associated with, if one is found in AD. All I really needed to do was this:
12Select name As DBName, suser_sname(owner_sid) As OnerNameFrom sys.databases;