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

 

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 – Account is currently locked out

Background

We have instituted complex passwords for our sa account.  Just the other day, unfortunately I locked it out.

Here is a background to a SQL Account creation step, the manifestation of locking it out, and one way to get things back well.

CREATE LOGIN


create login [hrdb]
with password = 'mycomplexpassword2015~'
, check_policy=on
, check_expiration=off

TRY LOGIN

With bad password

Tried connecting multiple times with bad password

Text

Login failed for user ‘hrdb’. ( Microsoft SQL Server, Error: 18456)

Image

LoginFailedForUser-BadPassword

Explanation

Received opaque error message.

 

With Good password

Finally, tried with good password.

Text

Login failed for user ‘hrdb’ because the account is currently locked out. The system administrator can unlock it. (Microsoft SQL Server, Error: 18486)

Image

LoginFailedForUser-GoodPassword

 

Explanation

Received revealing error message.

Remediate

Syntax


alter login [login]
with check_policy=off

alter login [login]
with check_policy=on

 

Sample


alter login [hrdb]
with check_policy=on

alter login [hrdb]
with check_policy=off

Summary

To give you a clue as to whether you trying a good or password word, you can look into the error message number.

Bad password returns 18456 while Good password returns 18486.

Also, keep in mind that the account will have to have the CHECK_POLICY option enabled for this to occur.

Microsoft – SQL Server – Cannot open user default database – Error 4064

Microsoft – SQL Server – Cannot open user default database – Error 4064

Cannot Open Default Database
Cannot Open Default Database

Since I already googled for this problem in the past, I already know what to do.

    Syntax:
       use <db-name>
       exec sp_change_users_login 'report'

    Sample:

       use [HRSales]
       exec sp_change_users_login 'report'

Result of running sp_change_users_login:


So the obvious next step is to align the user accounts listed.

Syntax:

    exec sp_change_users_login 'update_one'
                                , <user-name-in-currentDB>
                                , <sql-login>;

Sample:

    exec sp_change_users_login 'update_one', 'jazzyb', 'jazzyb';

Most of what I “blog” about is what I ‘ve learnt from others.

And, so it is easy to question the motivation.

One is that you give credit to others via Referencing them.

Another much subtle one is to give credence to other peoples work you come about via Google.

Yes, I wish Google takes me to exactly what I need, but as a lot of search is through Word proximity, sometimes one ends up at different places.

Another reason is that as human beings it is difficult to realize how much litte things add up and matter.

Here is one of my favorite from Barbara Bush:

You don’t just luck into things as much as you’d like to think you do. You build step by step, whether it’s friendships or opportunities – Barbara Bush

So do self a favor and ‘firewall’ future hurt by wishing others well and give a little.  If you do it a little at a time, it wouldn’t weigh you down, as much as you think it will.

Either you ‘re sharing or you ‘re hoarding. Once you decide to hoard, you will hurt when others don’t follow suit – Daniel Adeniji

Going back to the earlier point about the person who I should credit with this solution.  I really do not remember him\her; save for the fact I found him via Google.

But, I will leave you with some of my favorite Arundhati Roy quotes:

You know, I always believe that even among the best writers, there are selfish writers and there are generous ones. Selfish writers leave you with the memory of their book. Generous writers leave you with the memory of the world they evoked. To evoke a world, to communicate it to someone, is like writing a letter to someone that you love. It’s a very thin line. For me, books are gifts. When I read a book, I accept it as a gift from an author. When I wrote this book, I presented if as a gift. The reader will do with it what they want.

That’s what careless words do. They make people love you a little less.
― Arundhati RoyThe God of Small Things

Another world is not only possible, she is on her way. On a quiet day, I can hear her breathing.
― Arundhati Roy