Practical use of 'ALTER AUTHORIZATION ON DATABASE'

anushahanna
anushahanna used Ask the Experts™
on
How have you used the above command in your t-SQL code other than for DB diagrams.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
One practical scenario for usage of ALTER AUTHORIZATION ON DATABASE:

Lets assume we have user xyz in abc server. And xyz is the db_owner of that database.
And one day comes when user xyz leaves the organization and hence you take up the database at the owner role.

You cannot revoke access to that database for xyz user since he is the db_owner of that db.
Hence you need to issue ALTER AUTHORIZATION ON DATABASE statement to transfer the roles / privileges xyz owns to your user name.

Once it is done, xyz can be revoked and deleted from the user / logins.

Hope this clarifies.
Kindly Revert if you need more explanations.
Jagdish DevakuSr DB Architect
Commented:
Check this...

ALTER AUTHORIZATION ON DATABASE:

Cannot change the owner of system databases master, model, tempdb, the resource database, or a database that is used as a distribution database. The principal must be a login. If the principal is a Windows login without a corresponding SQL Server login, the principal must have CONTROL SERVER permission and TAKE OWNERSHIP permission on the database. If the principal is a SQL Server login, the principal cannot be mapped to a certificate or asymmetric key. Dependent aliases will be mapped to the new database owner. The DBO SID will be updated in both the current database and in sys.databases.

source: http://msdn.microsoft.com/en-us/library/ms187359.aspx

http://www.ss64.com/sql/authorize.html

Author

Commented:
How do I check the CONTROL SERVER & TAKE OWNERSHIP permission?
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
rrjegan17,
that was a good example.

Can you pl comment how this relates to the Database diagram problem SS seems to have
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_24574036.html

I am the owner of the DB, but not the files, is what the error says, for which I had to use ALTER AUTHORIZATION ON DATABASE
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
A sysadmin will have CONTROL SERVER permission.
 
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
anushahanna,
   I just gone through that question.
Can you confirm whether you have db_owner privilege for the database you mention.
If you have db_owner privileges, then you have complete privileges on all objects in that database and hence ALTER AUTHORIZATION is not required at all.

Author

Commented:
rrjegan17,
In that example, I am the owner of the DB. I can verify that from Exec SP_HelpDB dbname.

Can i check if I have db_owner privileges from a tSQL code?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Just issue

EXEC sp_helpuser 'abc'

replace abc with your user name to find out the privileges / permissions hold by this particular user account.

Hope this helps.

Author

Commented:
When I use the sp_helpuser, I get the message
"The name supplied <windows login> is not a user, role, or aliased login."

I login, of course, through my windows credentials; how else could I be set up?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Thanks JagdishDevaku for providing the reference.

anushahanna,
     Kindly tell us whether you have installed latest service packs in your machine. Just run

SELECT @@VERSION

and provide us the output to find whether you are running the latest service pack in your machine or not.

I believe running latest Service Packs would help you out in fixing this issue.

Author

Commented:
Microsoft SQL Server 2005 - 9.00.3310.00 (Intel X86)   Dec 19 2008 00:58:18   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Author

Commented:
JagdishDevaku ,
Should I run the following (is the parameters for sp_setapprole or do i need to tweak it)
-----------------
Go
sp_helpuser
Go
exec sp_setapprole 'approle', '123'
Go
sp_helpuser
Go
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Kindly apply SP3 from the link below:

http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4

And check it out again.
Jagdish DevakuSr DB Architect

Commented:
you need to give the necessary application roles and then execute is...

for more info on sp_helpuser refer the following links:

http://msdn.microsoft.com/en-us/library/ms188908.aspx

http://doc.ddart.net/mssql/sql70/sp_sa-sz_7.htm

for creating application roles:

http://msdn.microsoft.com/en-us/library/ms181491.aspx

http://msdn.microsoft.com/en-us/library/ms190998.aspx (info on application roles.)

I think installing sp3 as said by rrjegan17 might solve the issue or else try the above before applying sp3.


Author

Commented:
rrjegan17 & JagdishDevaku
Thanks for your kind help. I moved to SP3 (v9.0.4035), but still same issue.

I can create an app role. But Can you please guide me by code how to use that for this issue.
thanks
Jagdish DevakuSr DB Architect

Commented:
try the below code...

-- creating application role
use db_name
go
CREATE APPLICATION ROLE approle 
    WITH PASSWORD = '123' 
    , DEFAULT_SCHEMA = dbo;
 
Go
sp_helpuser
Go
exec sp_setapprole 'approle', '123'
Go
sp_helpuser
Go

Open in new window

Author

Commented:

when I ran the approle as an Application Role, All I get is another username called 'approle'.(see attached picture)

My problem may be this:

When I see in Security->Logins

I do not see my windows login there, but I see these options:

SRBSV\SQLServer2005SQLAgentUser$SRBSV$MSSQLSERVER
SRBSV\SQLServer2005MSSQLUser$SRBSV$MSSQLSERVER

Does SSMS has anything for me to investigate this a little more or understand better?

Could I be hidden in these? Both these are given 'public' roles.
approle.bmp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial