SQL Server – Database Permissions – List

Background

There are different levels of permissions in SQL Server.

There are concentric permissions.

Inclusive are Instance, database, schema, and object.

And, others that are more dependant on the object type.

Database Permissions

Code


SELECT
            [database]
              = db_name()

         ,  [username] 
              = tblGranteeDP.[name]

         ,  [login]     
             = suser_sname(tblGranteeDP.[sid])

         ,  [permissionClass]
             = tblDP.class_desc

         ,  [permissionName] 
            = tblDP.[permission_name]

         ,  [permissionState] 
            = tblDP.state_desc

         ,  [principalType] 
            = tblGranteeDP.[type_desc]

FROM sys.database_permissions AS tblDP

INNER JOIN sys.database_principals AS tblGrantorDP   
    ON tblGrantorDP.principal_id = tblDP.grantor_principal_id

INNER JOIN sys.database_principals AS tblGranteeDP
    ON tblGranteeDP.principal_id = tblDP.grantee_principal_id

LEFT OUTER JOIN sys.server_principals AS tblGranteeSP
    ON tblGranteeDP.principal_id = tblGranteeSP.principal_id

/*
    Identifies class on which permission exists.
*/
where (tblDP.class = 0)

/*
    Exclude those with mere CONNECT permission
*/
and  tblDP.permission_name not in 
        (   
            'CONNECT'
        )

order by
      db_name() asc
    , tblGranteeDP.[name] asc
    , tblDP.[permission_name] asc

Output

SQL Server – Security – Privileging & Tracking the Guest Account

Background

Each month we get databases from our Vendors.  And, we have to review data in those tables.

There are a couple of pathways to quickly granting accessing to our users.

In this post, we will grant access to the guest user.

 

SQL

Grant Access



grant connect to guest;

Review Access

Review Permissions grant/denied to Guest on Contextual Database

Code


declare @principal sysname

set @principal = 'guest'

; with cteObject
(
      objectID
    , [object]
    , schemaID
    , [schema]
)
as
(
    select
              tblSO.object_id
            , tblSO.[name]
            , tblSO.schema_id
            , tblSS.[name]

    from   sys.objects tblSO

    inner join sys.schemas tblSS

            on tblSO.[schema_id] = tblSS.[schema_id]
)
select 

          tblSP.[name]
        
        --, tblSDP.*

        , tblSDP.[state_desc]

        , tblSDP.[permission_name]

        , [objectClass]
            = tblSDP.class_desc

        , [object]
            = coalesce
                (
                     quoteName
                        (
                            cteO.[schema]
                        )
                    + '.'
                    + quoteName
                        (
                            cteO.[object]
                        )

                    , null
                )

            , [grantee]
                = SUSER_NAME(tblSDP.grantor_principal_id)

from   sys.database_principals tblSP

inner join sys.database_permissions tblSDP

    on tblSP.principal_id = tblSDP.[grantee_principal_id]

left outer join cteObject cteO

    on tblSDP.major_id = cteO.objectID

where tblSP.[name] = @principal

order by

    tblSP.[name] asc


Review Access for contextual database

Code

use master
go

set nocount on
go

set XACT_ABORT on
go

declare @tblResult table
(
      [database]    sysname
    , [principal]   sysname
    , [access]      sysname
    , [grantee]     sysname null
)

declare @command nvarchar(4000)

set @command =
                'if databasepropertyex(''?'', ''Collation'') is not null '
                + ' begin '
                + ' use [?]; '
                + ' select [database] = db_name(), tblSP.name,  tblSDP.[state_desc], SUSER_NAME(tblSDP.grantor_principal_id) '
                + ' from sys.database_principals tblSP '
                + ' inner join sys.database_permissions tblSDP '
                + ' on tblSP.principal_id = tblSDP.grantee_principal_id '
                + ' where tblSP.[name] = ''guest'' '
                + ' and tblSDP.[permission_name] = ''CONNECT'' '
                + ' and tblSDP.[state_desc]  = ''GRANT'' '
                + ' end '

insert @tblResult
(
      [database]    
    , [principal]   
    , [access]      
    , [grantee] 
)
exec master.dbo.[sp_MSforeachdb]
        @command1 = @command

select *

from   @tblResult tblR

order by 
    tblR.[database]

SQL Server – Scripting Drop User/Schema for empty schemas

Background

Early this morning I initiated a process to move a database from Production to QA.

As I tried to drop the users from the originating environment, I ran into the error pasted below:

drop user [LAB\ITV-IIS01$]
Msg 15138, Level 16, State 1, Line 13
The database principal owns a schema in the database, and cannot be dropped.

 

Origin

I know that the schema mentioned is a shell.  It was simply created as part of our user creation script.

Using SQL Server Management Studio, we can script the user creation script.

And, here is a sample.


CREATE USER [LAB\ITV-IIS01$]
FOR LOGIN [LAB\ITV-IIS01$] 
WITH DEFAULT_SCHEMA=[LAB\ITV-IIS01$]
GO

Remediation

Let us programmatically identify and script out those users.

SQL

Stored Procedure

dbo.sp_ScriptDropUsersWithSchemas


use master
go

if object_id('[dbo].[sp_ScriptDropUsersWithSchemas]') is null
begin

	exec('create procedure [dbo].[sp_ScriptDropUsersWithSchemas] as ')

end
go

-- exec sp_helpuser
alter procedure [dbo].[sp_ScriptDropUsersWithSchemas] 
as
begin

	;  with cteSystemUser
	(
		[username]
	)
	as
	(
		select 'dbo'
		union
		select 'guest'
		union
		select 'INFORMATION_SCHEMA'
		union
		select 'sys'
	)
	, cteSystemSchema
	(
		[schema]
	)
	as
	(
		select 'INFORMATION_SCHEMA'
		union
		select 'sys'
	)
	, cteObjectSchema
	(
		  [schema]
		, [object]
		, [schemaOwnerID]
		, [schemaOwnername]
	)
	as
	(
		select 
				  [schema] = tblSS.[name]
				, [object] = tblSO.[name]
				, [schemaOwnerID] = tblSS.principal_id
				, [schemaOwnername] = user_name(tblSS.principal_id)

		from   sys.schemas tblSS

		inner join sys.objects tblSO

				on tblSS.[schema_id] = tblSO.[schema_id]

		/*
			Skip System Schemas
		*/
		where  tblSS.[name] not in
					(
						select [schema]
						from   cteSystemSchema
					)
	)
	, cteUser
	(
		  [username]
		, [usertype]
		, [userSchemaDefault]
		, [userCreateDate]
		, [schema]
		, [sqlCommandDropSchema]
		, [sqlCommandDropUser]
	)
	as
	(
		select 
				  [username] = tblSDP.[name]
				, [usertype] = tblSDP.[type_desc]
				, [userSchemaDefault] = tblSDP.default_schema_name
				, [userCreateDate]= tblSDP.[create_date]

				, [schema] = tblSS.[name]

				, [sqlCommandDropSchema]
					= 'if schema_id(''' + tblSS.[name] + ''')' 
							+ ' is not null '
							+ ' begin '
							+ ' print ''drop schema '
							+ quoteName(tblSS.[name])
							+ ''' '
							+ '; '
							+ ' DROP SCHEMA '
							+ quoteName(tblSS.[name])
							+ ' end '

				, [sqlCommandDropUser]
					= 'if user_id(''' + tblSDP.[name] + ''')' 
							+ ' is not null '
							+ ' begin '
							+ ' print ''drop user '
							+ quoteName(tblSDP.[name])
							+ ''' '
							+ '; '
							+ ' DROP USER '
							+ quoteName(tblSDP.[name])
							+ ' end '

		from   sys.database_principals tblSDP

		left outer join sys.schemas tblSS

				on tblSDP.[name] = tblSS.[name]

		where tblSDP.[type_desc] in
				(
					  'SQL_USER'
					, 'WINDOWS_USER'
				)

		and  tblSDP.default_schema_name not in
				(
					'dbo'
				)
	)

	select 
			cteU.*

	from   cteUser cteU

	left outer join cteSystemUser cteSU

		on cteU.username = cteSU.username

	/*
		Skip System Users
	*/
	where cteSU.[username] is null

	/*
		Ensure that user does not own objects
	*/
	and not exists
		(	
	
			select 1

			from   cteObjectSchema cteOS

			where  cteU.[username] = cteOS.[schemaOwnername]

		)

end
go

exec sp_MS_marksystemobject '[dbo].[sp_ScriptDropUsersWithSchemas]'
go

Output

 

SQL Server – The dbo user

Background

There is a database that we restore nightly from Production.

The user has been doing a bit of acceptance testing and they called us this morning to let us know that they have too much access.

What a thing, user complaining of too much access?

 

TroubleShooting

Session

Run As User, Check User

CURRENT_USER

SQL

set nocount on
go

set XACT_ABORT on
go

use [pin]
go

DECLARE @login NVARCHAR(256)
DECLARE @user  NVARCHAR(256);

select 
			[STAGE]
				 = 'Before Context Switch'
		  , [SYTEM_USER_BEFORE_SWITCH] 
				= SYSTEM_USER
		  , [CURRENT_USER_IN_DB] 
				= CURRENT_USER
		  , [isSrvRoleMember] 
				= IS_SRVROLEMEMBER('sysadmin', SYSTEM_USER)	
;		

	
EXECUTE AS LOGIN = 'LAB\AnnMargaret'

select 
		    [STAGE] 
				= 'Within Context Switch'
		  , [SYTEM_USER] 
				= SYSTEM_USER
		  , [CURRENT_USER_IN_DB]
				= CURRENT_USER
		  , [isSrvRoleMember]
				= IS_SRVROLEMEMBER('sysadmin', SYSTEM_USER)	


/*

	select top 10 *
	from   dbo.pintbl

*/

--setuser
REVERT

select 
		    [STAGE] = 'Post Context Switch'
		  , [SYTEM_USER] = SYSTEM_USER
		  , [CURRENT_USER_IN_DB] = CURRENT_USER
		  , [isSrvRoleMember] = IS_SRVROLEMEMBER('sysadmin', SYSTEM_USER)	
;		


Image

Explanation
  1. We have 3 sections above
    • Section 1
      • Before Any User Context Change
        • I,the DBA, have all access
    • Section 2
      • Upon User Context Change
        • We use ‘SetUser’ or ‘Execute as Login’ to change the User
        • Queried to ensure user context change
          • SYSTEM_USER
            • The changed User Login account
          • CURRENT_USER
            • If user has db_owner privileges, then dbo returned
            • Else the user’s name in the contextual db
    • Section 3
      • Once change is reverted
        • We are back to ourselve

 

Database

DBO ROLE

sys.database_role_members

SQL

select 
		  [account]
			= tblSDP.[name]

		, [accountType] 
			= tblSDP.[type_desc]

		, [role] 
			= tblSDPR.[name]

from   sys.database_principals tblSDP

inner join sys.database_role_members tblSDRM

		on tblSDP.principal_id = tblSDRM.member_principal_id

inner join sys.database_principals tblSDPR

		on tblSDRM.role_principal_id = tblSDPR.principal_id

where tblSDPR.[name] = 'db_owner'

-- skip user dbo
and   tblSDP.[name] != 'dbo' 

;


Output

 

Explanation
  1. Only one account, dataVal, has db_owner privileges
    • dataVal
      • dataVal is a SQL Account
      • We are looking to track down a Windows Login

 

“DB OWNER”

 sys.databases

SQL


use [pin]
go

select 
		 [database]
			= tblSD.[name]
		, [ownerSID]
			= tblSD.[owner_sid]
		, [owner]
			= suser_sname(tblSD.[owner_sid])

from   sys.databases tblSD

where  tblSD.[name] = db_name()


Image

Explanation
  1. Database’s owner is sa

 

SQL Instance

Server Role Member

sp_helpsrvrolemember

SQL

declare @srvrolename  sysname

set @srvrolename = 'sysadmin'

EXEC [master].[dbo].sp_helpsrvrolemember
		  @srvrolename = @srvrolename

 

Image

Explanation
  1. Our Login is not listed as having sysadmin privileges

 

Account Membership

xp_logininfo

SQL

declare @acctname sysname
declare @option   sysname

set @acctname = 'BUILTIN\Administrators'
set @option = 'members';

EXEC [master].[dbo].xp_logininfo 
		  @acctname = @acctname
		, @option = @option 
		;

Output

Explanation

We found the account is part of the Local Administrator group.

 

Summary

Here is how the account we are tracking ended up with more information than we thought it would have:

  1. OS
    • Active Directory Account
      • Part of Machine’s Local Administrator group
  2. SQL Instance
    • SysAdmin
      • BUILTIN\Administrators has sysadmin privileges
  3. SQL Database
    • dbo
      • All logins that sysadmin have dbo access in each database

 

Special OS, AD Accounts and SQL Server

 

Background

Windows has a few special built-in accounts and groups.  And, same goes for Active Directory.

Unfortunately, how to reference those accounts within SQL Server is not always in reach.

 

Account & Group Names

Sam Account

The SAM Database is the database that houses the local computer’s users and groups.

 

Account Name What does it mean? Account Name  Create Login
Users BUILTIN\Users  CREATE LOGIN [BUILTIN\Users]
FROM WINDOWS;
Everyone All interactive, network, dial-up, and authenticated users are members of the Everyone group  \EVERYONE  CREATE LOGIN [\Everyone]
FROM WINDOWS
Authenticated Users The Authenticated Users identity Any user accessing the system through a logon process has the Authenticated Users identity.  NT AUTHORITY\Authenticated Users CREATE LOGIN
[NT AUTHORITY\Authenticated Users]
FROM WINDOWS

 

 

 

Active Directory Domain

Account Name What does it mean? Account Name  Create Login
Domain Users <domain-name>\Domain Users CREATE LOGIN [<domain-name>\Domain Users]
FROM WINDOWS;

 

 

 

Anecdotes

Authenticated Users

Randy Franklin Smith

  1. Understanding the Authenticated Users Group
    Link

    Microsoft created the Authenticated Users group in response to fears that Anonymous logons could gain access to objects for which Everyone (another special security principal) has access. I don’t recommend using the Authenticated Users group for controlling permissions because it includes local accounts, which are a bad practice to use because you can’t centrally manage them at the domain level, and they use NT LAN Manager (NTLM) authentication rather than the stronger Kerberos. Also, the membership of Authenticated Users changes dynamically when you create a trust to another domain. When you want to give all users in a domain access to a resource, I recommend that you use the Domain Users group, which limits membership to the domain. If you need to give all users in a forest access to a resource, create a universal scope group called Forest Users and add each domain’s Domain Users group as a member.

 

Users

SS64.com

  1. Windows Built-in Users and Default Groups
    Link

    A built-in group. After the initial installation of the operating system, the only member is the Authenticated Users group. When a computer joins a domain, the Domain Users group is added to the Users group on the computer. Users can perform tasks such as running applications, using local and network printers, shutting down the computer, and locking the computer. Users can install applications that only they are allowed to use if the installation program of the application supports per-user installation.

 

 

Reference

  1. Microsoft – TechNet Magazine
    • TechNet Magazine > Tips > Windows Server 2008
      Link
    • Windows Server 2008 > Understand Implicit Groups and Identities
      Link
  2. WeaselFire Ramblings
    • Everyone isn’t everyone
      Link
  3. Exploit Database
    • Ubisoft Uplay 4.6 – Insecure File Permissions Privilege Escalation
      Link
  4. Varonis.com
    • Rob Sobers
      • The Difference Between Everyone and Authenticated Users
        Link
  5. Windows IT Pro
    • Jan De Clercq
      • What’s the scope of the built-in Authenticated Users group in a multi-forest Active Directory (AD) environment?
        Link
    • Randy Franklin Smith
      • Understanding the Authenticated Users Group
        Link
  6. Stack Overflow
    • StackExchange.com
      • Windows groups and permissions: Authenticated Users group meaning
        Link
  7. ss64.com
    • Windows Built-in Users and Default Groups
      Link

SQL Server – Life after Database Restore – Inherited Security

Background

Once a database is restored there a few things one has to do.

With SQL based accounts, one of those things is to realign the database users.

With Windows Authenticated users, things should be OK if in same Windows Domain.

On the other hand, if different domain we have to grant access to targeted Logins, as well.

 

Inherited Security

Let us talk about what I will call “Inherited Security“.

Outline

  1. Restore Database
  2. Create Login
    • Normal login, not sysadmin
  3. Query Login as the non-privilege Login
    • Using “Execute As Login” query one of the tables in the restored database
    • Errored out indicating Login does not have access to restored database
  4. Grant Server role permission to created Account
    • Only applicable if SQL Version is v2014
    • The specific SQL Instance privilege we will like to grant is “Connect any database
    • Again, please keep in mind that “Connect any database” was instituted in v2014
      • Docs / S​QL / T-​SQ​L / statements / GRANT Server Permissions (Transact-SQL)
        Link
  5. On targeted database, allow guest access
    • On targeted database, grant connect access to guest
    • In SQL Server, the guest access is anyone who is registered in SQL Server, but does not have an account on the targeted database
    • Grant specific permissions to guest account

Code

Create Login



--Norton by Symantec // Password Generator
--https://identitysafe.norton.com/password-generator/#
--s19miewRoest

declare @loginName	varchar(30)
declare @password	varchar(30)

set @loginName = 'BISUser'
set @password = 's19miewRoest'

if suser_id(@loginName) is null
begin

	print 'Create Login ' + @loginName + ' ...'

	create login [BISUser]
		with password = 's19miewRoest'
		, check_policy=off
		, check_expiration= off

	print 'Create Login ' + @loginName
end
go

 

Query restored data under guise of normal Login

Code

use [tempdb]
go

EXECUTE AS LOGIN = 'BISUser'
go

begin tran

	SELECT TOP 1 *

	FROM [INDThirdParty].[dbo].[Contacts]

rollback tran
go

REVERT
go

 

Error Image

 

Error Text

Msg 916, Level 14, State 1, Line 9
The server principal "BISUser" is not able to access the database "INDThirdParty" under the current security context.

Remediation – Grant Connect Any database

Code

Grant Connect Any database to Principal
Code

use [master]
GO
GRANT CONNECT ANY DATABASE TO [BISUser]
GO

Retried querying data and got new error message
Error

 

Msg 229, Level 14, State 5, Line 9
The SELECT permission was denied on the object 'Contacts', database 'INDThirdParty', schema 'dbo'.

 

Grant permission on targeted database to all users

On targeted database, tried granting appropriate permissions to all users

Code

--v2005 and above
exec sp_addrolemember @rolename = 'db_datareader', @membername = 'public'
go

--v2012  and above
ALTER ROLE db_datareader
  ADD MEMBER [public]
go

Error

 


Msg 15405, Level 16, State 1, Line 36
Cannot use the special principal 'public'.
Msg 15405, Level 16, State 1, Line 41
Cannot use the special principal 'public'.

Conclusion

So we see that we need more.

 

Remediation – Allow Guest access

Code

Revoke “Connect Any Database” From Principal
Code

use [master]
GO

REVOKE CONNECT ANY DATABASE FROM [BISUser]
GO

On Targeted Database, Enable Guest Access
Code

use [INDThirdParty]
GO

GRANT CONNECT TO [Guest]
GO

Retried querying data and got the permission error message again
Error

Msg 229, Level 14, State 5, Line 9
The SELECT permission was denied on the object 'Contacts', database 'INDThirdParty', schema 'dbo'.
Grant permission on targeted database to guest

On targeted database, grant appropriate permissions to guest

Code

--v2005 and above
exec sp_addrolemember @rolename = 'db_datareader', @membername = 'guest'
go

--v2012  and above
ALTER ROLE db_datareader
  ADD MEMBER [guest]
go

Retry query

On targeted database, grant appropriate permissions to guest

Code

use [tempdb]
go

EXECUTE AS LOGIN = 'BISUser'
go

begin tran

	SELECT TOP 1 *

	FROM [INDThirdParty].[dbo].[Contacts]

rollback tran
go

REVERT
go

SQL Server Profiler

 

Explanation
  1. The NTUserName stays as the logged on user
  2. The LoginName changes as we transition in with “EXECUTE AS LOGIN” and out with “REVERT

Conclusion

We are good!

 

Dedicated

Dedicating to Matt Bowler & Dan,  a commenter on Matt’s Post

Restore database permissions
Posted On: 2013-Jan-10th
Link