How to List All Permissions for SQL Server Fixed Database and Server Roles

sql serversql-server-2012

I am trying to list all current permissions for db_owner and sysadmin for SQL Server 2012. I found these SPs:

EXEC sp_srvrolepermission 'securityadmin'
EXEC sp_dbfixedrolepermission 'db_owner'

However, these are deprecated and only accurate as of SQL Server 2000. Is there an equivalent mechanism to accomplish the same thing today?

Best Answer

Neither of these roles has an enumerated list of permissions. db_owner has all permissions in a database, and sysadmin is exempt from permissions checking on both the server and every database. There are a handful of system procedures that are hardcoded to check for sa or perhaps dbo.

Related Solutions

Sql-server – Login Failed for Domain\ServerName$ when trying to create a new SQL Server Reporting Services Application in SharePoint 2013

Add BOTH Domain\Servername$ login and NT AUTHORITY\Network Service login to SQL Server instance giving you above problem and give both logins the right permissions.

If you are working in a Spanish environment or another language try adding NT AUTHORITY\Servicio de Red or whatever the equivalent is in your language.

For steps on adding logins see links: http://support.microsoft.com/kb/889646/en-us and http://www.c-sharpcorner.com/UploadFile/specialhost/how-to-fix-login-failed-for-user-nt-authority-iusrs-in-share/ and https://stackoverflow.com/questions/2806438/login-failed-for-user-domain-machinename

If you have a SQL Server Instance with logins that have been working for you when accessed by application pools and the like, you might want to check which logins are not in the troublesome SQL Server instance and that you might need.

Other comments:

Ok, even though the error said: Login Failed for user 'Domain\Servername$' and I added that login to the problematic SQL Server instance per what I'd seen described on the internet, it seems that the login I ALSO needed to add was NT AUTHORITY\Network Service.

I basically decided to add any logins that were in my normal SQL Server instance that I was using for the regular SharePoint databases and that weren't in the SQL Server instance I was using for SharePoint Reporting Services to the latter. Something else that tripped me up when I was looking originally to add the NT AUTHORITY\Network Service login was that I couldn't find it. It turns out I had to add it in Spanish as NT AUTHORITY\Servicio de red, which is wierd because the app pool has its Identity in English. I guess this happens when some systems have English as default language and some others have Spanish

Sql-server – SQL Server grant permissions to STANDBY database

Below steps should work :

Create windows login on ServerA
Create user in database mapped to login
Drop login on ServerA - (OPTIONAL -if you want to have that login intact then leave it, else drop it).
Grant any required permissions to user in database

Create login on log shipping ServerB

a. If using SQL authentication then create login while specifying SID from ServerA

b. If using Windows authentication, create login without specifying SID

 ---To query for the SID on the ServerA:

  Select SID
  From sys.database_principals
  Where name = 'Test'

 ---To create the login on the ServerB :

  Create Login [Test]
  With SID = '<put SID from query here>',
  Password = '<add password here>'

Make sure log shipping jobs have completed one full run since completion of step 4

Best Answer

Related Solutions

Sql-server – Login Failed for Domain\ServerName$ when trying to create a new SQL Server Reporting Services Application in SharePoint 2013

Sql-server – SQL Server grant permissions to STANDBY database

Related Question