The SQL Dude!

I was working with one of my customers and came across a unique scenario, so I thought it share it out here. My customer had multiple Active Directory groups which were added as logins in SQL Server. Some of these groups had access to only certain databases and some had sysadmin rights. It so happens that certain users were part of multiple AD groups, so we need to track down the security chain to see which of these groups were granting access & permissions to these databases.

Now, if a Windows login is part of multiple groups, then during the initial login phase, the access token is created. This access token contains all the groups that this login is a part of. There are 2 possible scenarios here:-

1. The Windows user is also a login in SQL Server as well as the Group is a login.

2. The Windows user does not have a login in SQL Server but only via the Group.

In case #1, the user will access based on the login’s access rights. In case #2, if the user were part of multiple groups how do we determine which group was granting his permissions inside a database.

1. Read the login Token

Luckily, we have a view that lets you see what groups are part of a login’s token called as sys.login_token. This displays the login token for the currently logged in session. We need to filter this to report only those groups which are in the login’s token and which are also a valid login in SQL Server.

select * from sys.login_token where sid in (select sid from sys.server_principals)

Once you have this information, you can then focus on these groups listed here to see what access rights they have on the database in question. Here is a sample query to check your access rights on objects in a database.

select @@SERVERNAME '@@servername', db_name() 'dbname',
SYSTEM_USER 'system_user', CURRENT_USER 'CURRENT_USER', session_user 'session_user' ,  SUSER_SNAME()  'SUSER_SNAME()',
* from fn_my_permissions (null, 'database')

2. DBO User and Login Mapping

In SQL Server each database has a user called as ”DBO”. This is a built-in user who is the owner of the database. The DBO user has full-control of the database and the access rights are not explicitly visible on each database, but understood to be granted. So if my windows login CONTOSO\testuser was the owner of database “AdventureWorks”, then within this database AdventureWorks, my login contoso\testuser will be known as DBO. There can only be 1 DBO user in a database. Don’t get confused with db_owner database role. This role can be used to grant other logins dbo rights, meaning all access rights are the same, but those logins will be added as users to the database. But the database owner itself will not have a user inside the database, because he is the DBO user.

Using SQL Management Studio, we can find the login which is mapped to the DBO user in a database,

There is 1 scenario, where the DBO user can be mapped to an invalid login. If the database was attached from one server where loginA was the DBO and the moved to another server where loginA does not even exist, then you will see that loginA is still the DBO. Of course, during the attach you can change the db_owner, but this is not something that people generally do.

You can use the following script to identify all the databases whose DBO user is mapped to an invalid login. Once identified you will have to correct is using any of the below commands.

use <dbname>
sp_changedbowner 'sa'

use <dbname>
ALTER AUTHORIZATION ON DATABASE::AdventureWorks TO [CONTOSO\testuser]

T-SQL Script

--Script : Verify the database owner and DBO User owner are the same
--When a login is defined as the DBO user, he/she has FULL control within the database
set nocount on
go
Create table #TmpTableSec1 (database_name varchar(100), Owner varchar(100))
Create table #TmpTableSec2 (database_name varchar(100), principal varchar(50), DBO_Owner_Login varchar(100))
Create table #TmpResult (database_name varchar(100), principal varchar(50), DBO_Owner_Login varchar(100))
DECLARE DBCURSOR CURSOR FOR
select name 
from sys.databases where state=0 and name not in ('tempdb','model','master','msdb')
Declare @name varchar(50)
Declare @cmd  varchar(200)
Declare @dbowner varchar(100)
Declare @DBO varchar (100)
OPEN DBCURSOR
FETCH NEXT FROM DBCURSOR INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
    --print 'Database --> ['+@name+']'
    set @cmd = 'select name,  suser_sname(owner_sid) from master.sys.databases where name = '''+@name+''''
    --select @cmd
    insert #TmpTableSec1 exec (@cmd)
    --select @dbowner = (select suser_sname(owner_sid) from master.sys.databases where name = @name)
    set @cmd = 'use '+ @name +'
    select db_name(), name, suser_sname(sid)
    from sys.database_principals where name = ''dbo'''
    --select ''@DBO'' = (select suser_sname(sid) from sys.database_principals where name = ''dbo'') '
    INSERT #TmpTableSec2 exec (@cmd)
    --print ''
FETCH NEXT FROM DBCURSOR INTO @name
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
go
insert into #TmpResult
select a.database_name, a.Owner, b.DBO_Owner_Login from #TmpTableSec1 a
join #TmpTableSec2 b
on a.database_name = b.database_name
print '--- Database Owner & DBO User Mapping ---'
print '*****************************************'
select * from #TmpResult
print '--- Orphaned DBO Logins ---'
print '***************************'
select * from #TmpResult
where DBO_Owner_Login not in (select name from sys.server_principals) or  DBO_Owner_Login is null
drop table #TmpTableSec1
drop table #TmpTableSec2
drop table #TmpResult
set nocount off

In the result, pay careful attention to the second output as that lists all the DBO users whose login does not exist on this instance of SQL Server, along with the database name. You will need to correct that using the any of the 2 methods listed above for each of the database listed in the 2nd output.

That’s all for now folks. Stay safe, stay secure!