MySQL Forums
Forum List  »  Microsoft SQL Server

Re: Lost ability to retrieve the schemes from MSSQL once we replaced our MSSQL 2008 R2 version with 2017 version
Posted by: Jacob Nikom
Date: February 28, 2018 04:59PM

Hello Milosz,

Thanks again for your attention and help.

I think I do have required permissions on necessary databases.
This is what required by SQL Server:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-databases-transact-sql

Permissions

Requires CREATE DATABASE, or ALTER ANY DATABASE, or VIEW ANY DEFINITION permission, and must have access permission to the database. Cannot be denied VIEW ANY DEFINITION permission.

Here is how I got to see the permissions I currently have in SQL Server 2017:

Here is the list of all databases on that server with some access privileges:

SELECT name, database_id, user_access, user_access_desc, is_read_only,
compatibility_level FROM sys.databases;

--------------------------------------------------------------------------------------------------
# name database_id user_access user_access_desc is_read_only compatibility_level
--------------------------------------------------------------------------------------------------
1. master 1 0 MULTI_USER 0 140
2. tempdb 2 0 MULTI_USER 0 140
3. model 3 0 MULTI_USER 0 140
4. msdb 4 0 MULTI_USER 0 130
5. artfactDB2 5 0 MULTI_USER 0 140
6. analysisDB 6 0 MULTI_USER 0 140
7. DBA 7 0 MULTI_USER 0 140
8. dataArchive 8 0 MULTI_USER 0 140
9. packageDB 9 0 MULTI_USER 0 140
10. rfcAnalysis 10 0 MULTI_USER 0 140
11. CoreDB_placeholder 11 0 MULTI_USER 0 140
--------------------------------------------------------------------------------------------------

As you see each database has MULTI_USER access that is the default database user access mode. According to documentation, in this mode any user who have permission to access the database can access the database.

Next, I go into each databases and check my permissions over there:


1.
USE master;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-------------------------------------------------------------------------
entity_name subentity_name permission_name
-------------------------------------------------------------------------
database CONNECT
database VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database VIEW ANY COLUMN MASTER KEY DEFINITION
-------------------------------------------------------------------------

2.
USE tempdb;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-------------------------------------------------------------------------
database CONNECT
database VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database VIEW ANY COLUMN MASTER KEY DEFINITION
-------------------------------------------------------------------------

3.
USE model;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-------------------------------------------------------------------------
Msg 916, Level 14, State 1, Line 1
The server principal "OSDprod" is not able to access the database "model" under the current security context.
-------------------------------------------------------------------------

4.
USE msdb;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-------------------------------------------------------------------------
database CONNECT
database VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database VIEW ANY COLUMN MASTER KEY DEFINITION
-------------------------------------------------------------------------

5.
USE artfactDB2;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-------------------------------------------------------------------------
entity_name subentity_name permission_name
-------------------------------------------------------------------------
database CONNECT
database SELECT
database VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database VIEW ANY COLUMN MASTER KEY DEFINITION
database VIEW DEFINITION
-------------------------------------------------------------------------

6.
USE analysisDB;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-------------------------------------------------------------------------
entity_name subentity_name permission_name
-------------------------------------------------------------------------
database CONNECT
database SELECT
database INSERT
database UPDATE
database DELETE
database VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database VIEW ANY COLUMN MASTER KEY DEFINITION
-------------------------------------------------------------------------

7.
USE DBA;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-------------------------------------------------------------------------
database CONNECT
database SELECT
database VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database VIEW ANY COLUMN MASTER KEY DEFINITION
-------------------------------------------------------------------------

8.
USE dataArchive;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-------------------------------------------------------------------------
database CONNECT
database SELECT
database VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database VIEW ANY COLUMN MASTER KEY DEFINITION
-------------------------------------------------------------------------

9.
USE packageDB;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-------------------------------------------------------------------------
database CONNECT
database SELECT
database VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database VIEW ANY COLUMN MASTER KEY DEFINITION
-------------------------------------------------------------------------

10,
USE rfcAnalysis;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-------------------------------------------------------------------------
entity_name subentity_name permission_name
-------------------------------------------------------------------------
database CREATE TABLE
database CREATE VIEW
database CREATE PROCEDURE
database CREATE FUNCTION
database CREATE RULE
database CREATE DEFAULT
database BACKUP DATABASE
database BACKUP LOG
database CREATE TYPE
database CREATE ASSEMBLY
database CREATE XML SCHEMA COLLECTION
database CREATE SCHEMA
database CREATE SYNONYM
database CREATE AGGREGATE
database CREATE ROLE
database CREATE MESSAGE TYPE
database CREATE SERVICE
database CREATE CONTRACT
database CREATE REMOTE SERVICE BINDING
database CREATE ROUTE
database CREATE QUEUE
database CREATE SYMMETRIC KEY
database CREATE ASYMMETRIC KEY
database CREATE EXTERNAL LIBRARY
database CREATE FULLTEXT CATALOG
database CREATE CERTIFICATE
database CREATE DATABASE DDL EVENT NOTIFICATION
database CONNECT
database CONNECT REPLICATION
database CHECKPOINT
database SUBSCRIBE QUERY NOTIFICATIONS
database AUTHENTICATE
database SHOWPLAN
database ALTER ANY USER
database ALTER ANY ROLE
database ALTER ANY APPLICATION ROLE
database ALTER ANY COLUMN ENCRYPTION KEY
database ALTER ANY COLUMN MASTER KEY
database ALTER ANY SCHEMA
database ALTER ANY ASSEMBLY
database ALTER ANY DATABASE SCOPED CONFIGURATION
database ALTER ANY DATASPACE
database ALTER ANY EXTERNAL DATA SOURCE
database ALTER ANY EXTERNAL FILE FORMAT
database ALTER ANY EXTERNAL LIBRARY
database ALTER ANY MESSAGE TYPE
database ALTER ANY CONTRACT
database ALTER ANY SERVICE
database ALTER ANY REMOTE SERVICE BINDING
database ALTER ANY ROUTE
database ALTER ANY FULLTEXT CATALOG
database ALTER ANY SYMMETRIC KEY
database ALTER ANY ASYMMETRIC KEY
database ALTER ANY CERTIFICATE
database ALTER ANY SECURITY POLICY
database SELECT
database INSERT
database UPDATE
database DELETE
database REFERENCES
database EXECUTE
database ALTER ANY DATABASE DDL TRIGGER
database ALTER ANY DATABASE EVENT NOTIFICATION
database ALTER ANY DATABASE AUDIT
database ALTER ANY DATABASE EVENT SESSION
database KILL DATABASE CONNECTION
database VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database VIEW ANY COLUMN MASTER KEY DEFINITION
database VIEW DATABASE STATE
database VIEW DEFINITION
database TAKE OWNERSHIP
database ALTER
database ALTER ANY MASK
database UNMASK
database EXECUTE ANY EXTERNAL SCRIPT
database ADMINISTER DATABASE BULK OPERATIONS
database CONTROL
-------------------------------------------------------------------------

11.
USE CoreDB_placeholder;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-------------------------------------------------------------------------
Msg 916, Level 14, State 1, Line 1
The server principal "OSDprod" is not able to access the database "CoreDB_placeholder" under the current security context.
-------------------------------------------------------------------------

Which permissions do you think are sufficient for retrieving the schema?

I think at least those three databases - artfactDB2, analysisDB and rfcAnalysis - should be retrievable.

Is this information sufficient, or you want me to investigate further?

Best regards,

Jacob Nikom

Options: ReplyQuote


Subject
Written By
Posted
Re: Lost ability to retrieve the schemes from MSSQL once we replaced our MSSQL 2008 R2 version with 2017 version
February 28, 2018 04:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.