Practical use of 'ALTER AUTHORIZATION ON DATABASE'

How do I check the CONTROL SERVER & TAKE OWNERSHIP permission?

rrjegan17,
that was a good example.

Can you pl comment how this relates to the Database diagram problem SS seems to have
https://www.experts-exchange.com/questions/24574036/security-does-not-allow-to-work-on-Database-Diagrams-in-SS2005.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

A sysadmin will have CONTROL SERVER permission.
 

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.

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?

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.

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?

This was already logged at

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125814

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.

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)

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

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.

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.

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

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

Select allOpen in new window

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