Grant select access to specific object or tables

 We can grant select access to specific Tables / Objects in three ways 

Create Login on SQL server instance 

In my case Login name Appteam

Don't Grant any server role to Login

I need fetch data from TEST_Mirr database 

Don't grant any database role

Default public will be there 

On Database user will be created automatically 

Here for Appteam not showing any tables on the TEST_Mirr database 

1) using T SQL 

Granted select on below two tables for Appteam user

use TEST_Mirr 

Grant select on conn1 to Appteam

Grant select on conn2 to Appteam

2) Using GUI 

Using Admin session go to user properties 

Click on search and select all objects of the types and click ok , it will pop up new dialog box

Select Tables and click OK

This time i am going grant select access on Output_Field table 

After granting select permissions on Output_Field table

3) Using Table Properties 

For which we need to grand select on the table go to that table properties

Go to permissions tab >>> search for a user >>> grant select as shown in below screenshots 

Now we can see all the tables which we granted select access

The feature “Scale-out deployment” is not supported in this edition of Reporting Services

Step1 :Connect to SSMS

Step2: Open New query window 

Step3: run below query

Use Report server

go

Select * from Keys

Step4: List all the keys that needs to be deleted, except the NULL one. 

Note: Don't delete NULL this is current one.

Step5:  Execute below command

Delete from Keys where instalationID=<>

Step 6: Restart SSRS 

SQL Server Performance issue - With DB Compatibility upgrade from 120 to 150

Worked Solution:

We turned on the Legacy Cardinality Estimation on the database properties. 

If anyone has other solutions please comment.

SQL Server 2022 Services Failed To Come Online.

Error: Script level upgrade for database ‘master’ failed because upgrade step msdb110_upgrade.sql encountered error 926, state 1, severity 25

The above error will be present in the Error log and the solution will be to start the SQL Server either from /T902 or add this in strtup parameter to permently resolve this issue. 

This will bypass the upgrade script and starts the SQL Server. 

Reference:

https://blog.sqlauthority.com/2019/04/04/sql-server-script-level-upgrade-for-database-master-failed-because-upgrade-step-msdb110_upgrade-sql-encountered-error-8649-state-1-severity-17/

Failed to initialize sqlcmd library with error number -2147467259.

This error has too many options to fix, one of them is below resolution - 

In our case we were using a dynamic query which will send some results over the email with help of dynamic execution. when the query was executed using sp_send_mail it used to fail.

Failed to initialize sqlcmd library with error number -2147467259.

After couple of hours identified that query results using linked server was causing the issue. 

An error occurred during Service Master Key decryption There is no remote user ' ' mapped to local user '(null)' from the remote server ' '. (Microsoft SQL Server, Error: 33094)

To fix this we need to run 

        ALTER SERVICE MASTER KEY FORCE REGENERATE

Once the above command executed linked server started working properly. The dynamic query started to work smoothly, which inturn resolved the issue. 

SSRS not starting - unable to connect to the report server

 After the windows upgrade few cases WMI RS integration will be corrupted, during that time we get the error "Unable to connect to the Report Server "

Solution : 

Based on the SQL Server Version the MOF file will be located in different locations, in our case it was on D drive.

1. Open CMD prompt with elevated privileges

2. Run below command 

mofcomp "D:\SQL Server Files\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\\reportingservices.mof"

=============== This will fix the issue =================

SQL Server DB Object, Job and DB Owner Details

 -- Below will provide the Job Owner Details

SELECT s.name AS JobName, l.name AS JobOwner, s.enabled

FROM msdb..sysjobs s

LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid

WHERE l.name IS NOT NULL and l.name in ('Domain\User' )

ORDER by l.name

 

-- Below will provide DB Owners

SELECT name,

        suser_sname( owner_sid ) AS DBOwnerName

FROM master.sys.databases

WHERE suser_sname( owner_sid )in  ('Domain\User' )

 

--- Below will provide Each Object Owners 

;with objects_cte as

(

    select

        o.name,

        o.type_desc,

        case

            when o.principal_id is null then s.principal_id

            else o.principal_id

        end as principal_id

    from sys.objects o

    inner join sys.schemas s

    on o.schema_id = s.schema_id

    where o.is_ms_shipped = 0

    and o.type in ('U', 'FN', 'FS', 'FT', 'IF', 'P', 'PC', 'TA', 'TF', 'TR', 'V')

)

select

    cte.name,

    cte.type_desc,

    dp.name

from objects_cte cte

inner join sys.database_principals dp

on cte.principal_id = dp.principal_id

where dp.name in  ('Domain\User' ) or dp.name <>'dbo'