Sql-server – alter authorization in SQL Server

authorizationdatabase-diagramssql server

I will to made a database diagram in my SQL Server Management Studio. But I have this warning that said:

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Can you help me whit this error? How can I give myself (hein_) the promision to continue?

@JeffersonBElias suggested me to execute this code:

select suser_sname(owner_sid) as usersname from sys.databases where name = 'FreeTimeDBSSA'

What me gives this:

+-----+-----------------------+
|     |       usersname       |
+-----+-----------------------+
|  1  | DESKTOP-3BMCMPC\hein_ |
+-----+-----------------------+

Here you have my Object Explorer:

Best Answer

I have found it whit a hint from @JeffersonB.Elias. I've execute this code:

ALTER AUTHORIZATION ON DATABASE::FreeTimeDBSSA TO sa;
GO

Related Solutions

Sql-server – Exporting from SQL Server 2005 for import into ER Studio 9.0

I can't believe I didn't actually google the exact error message. There's a lesson to be learned there. Upon doing so, I learned that similar errors occur in other applications. The cause is non-ASCII codes. I was exporting from SQL in Unicode.

Here is the full solution: In SQL:

Right-click on the desired db, choose Tasks > Generate Scripts
Select the desired database, next
Leave the default options (I've tried a few things, none worked), next
Select Tables only (I don't need anything except the tables, their fields, keys, and relationships)
Select all tables (or 4 tables, it doesn't matter)
Choose "Script to file", enter a file name
Under Save as: choose ANSI text (Unicode breaks the crap out of ER/Studio)
Finish

Then import into ER/Studio. My import has been running for 2 hours so far.

Next up, trying to figure out how to make an ordered list on this site. I'm so full of fail. :)

MySQL Views Authorization

Here is the definition of information_schema.views:

mysql> show create table information_schema.views\G
*************************** 1. row ***************************
       Table: VIEWS
Create Table: CREATE TEMPORARY TABLE `VIEWS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VIEW_DEFINITION` longtext NOT NULL,
  `CHECK_OPTION` varchar(8) NOT NULL DEFAULT '',
  `IS_UPDATABLE` varchar(3) NOT NULL DEFAULT '',
  `DEFINER` varchar(77) NOT NULL DEFAULT '',
  `SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Here is how you can count the views per database per user

SELECT COUNT(1) ViewCount,table_schema,definer
FROM information_schema.views
GROUP BY table_schema,definer;

I am not that sure about access/denial of views. In the information_schema.views table, there is field called SECURITY_TYPE (which have values DEFINER and INVOKER).

If a View has SECURITY_TYPE as INVOKER, everybody and his grandmother can SELECT from that View. My guess is that you would have to set the SECURITY_TYPE to DEFINER to restrict everyone from running a SELECT from that View. Of course any user with SUPER privilege and the definer can a SELECT on that View.

Changing the SECURITY_TYPE is a little painful.

You can mysqldump all views. Here is a post I made back on July 26, 2011 : Modify DEFINER on Many Views. @DTest had an even more concise answer using ALTER VIEW.

Edit the output file in vi or some other editor to change the SECURITY TYPE of any View you wish. Then, jus reimport the view from that text file.

Give it a Try !!!

Best Answer

Related Solutions

Sql-server – Exporting from SQL Server 2005 for import into ER Studio 9.0

MySQL Views Authorization

Related Question