Skip to the content

Martin Sentker

  • Willkommen
  • Blog
  • Über mich
  • Consulting
    • Wirtschaftszweige
    • Werkzeuge
    • Skills
    • Historie der durchgeführten Projekt
  • Impressum
  • Disclaimer
  • Sprache: Deutsch
    • Deutsch Deutsch
    • English English
20
Jun
Off

(English) on SQL Server security: database owner

by Martin

Leider ist der Eintrag nur auf Amerikanisches Englisch verfügbar. Der Inhalt wird unten in einer verfügbaren Sprache angezeigt. Klicken Sie auf den Link, um die aktuelle Sprache zu ändern.

When restoring a database from a different stage or derver don’t forget to check the login for dbo database user!

If the following statement doesn’t return e.g. sa but a developer login

SELECT SUSER_NAME(principal_id) FROM sys.database_principals WHERE name = ‚dbo‘;

you can change the login for the dbo user using the following statement:

ALTER AUTHORIZATION ON DATABASE::[THE_DATABASE_HERE] TO [sa];

Background:

The dbo user is the owner of the dbo schema which often contains many or all database objects.
Furthermore by default the user is member of the db_owner role.
The owner of the database is granted in parallel to the normal permission system !

To check effective login permisssions there are multiple options:

SELECT IS_MEMBER(‚db_owner‘)

will return 1(true) if the logged in user is member of the db_owner role (database scope!).

You may also use

use DATABASE_NAME_HERE

— optionally EXECUTE AS LOGIN = ‚MYDOMAIN\some.user‘;

select * from fn_my_permissions(null, ’server‘)

select * from fn_my_permissions(null, ‚database‘)

select * from fn_my_permissions(’schema.SchemaObject‘, ‚OBJECT‘)

— optionally REVERT;

 

A person may be member in multiple AD groups and therefore might have permisisons resulting from multiple AD group logins:

EXEC xp_logininfo ‚domain\userId‘,‚all‘; — elevated permissions required

alternatively off course also these (good for permission checking in views, procedures etc.)

SELECT IS_MEMBER(‚Domain\userId‘)

SELECT IS_MEMBER(‚Domain\ADGroup‘)

SELECT IS_MEMBER(‚databaserole‘)

 

 

 

 

 

Security SQL Server

Schlagwörter: AD, dbo, login, member, permission

Schlagworte

2 factor auth AD bot Databinding dbo EF6 Entity Transaction encryption extended protection Force Encryption Issue knockout.js login member oleDB permission SQL Server

Kategorien

  • .Net
  • Asp.Net Security
  • Azure
  • C#
  • MVC
  • MVVM
  • Node.js
  • Reporting
  • Security
  • SQL Server
  • Web
  • Windows
  • WinForms
  • WPF

Meta

  • Anmelden
  • Feed der Einträge
  • Kommentare-Feed
  • WordPress.org
Mai 2022
M D M D F S S
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
« Dez    

Archive

  • Dezember 2018
  • November 2018
  • April 2018
  • Dezember 2017
  • Juni 2017
  • April 2017
  • März 2017
  • Dezember 2016

Proudly powered by WordPress | Theme: Connect by Themes4WP