permission to access database properties

Question

I have created a user in sql server for which i have given server role as public only and in user mapping i have given db_datareader and public only.This i have done because user can only access the database and not modify anything.By this user i am not able to see database properties.So what changes should i do so that database properties also can be seen by user?

Answer 1

Hi

You should be able to see the Database properties. Which version of SQL Server you are using.

Are you getting any Error message when Right click the Database properties

---

Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::

Answer 2

Hi,

To do read only database user in SQL Server Management Studio 2008 do below steps:

1. Connect to your database server.
2. Expand Security > Logins.
3. Right-click on the user who will be set as having read-only access .
4. Select Properties.
5. Select User Mapping.
6. Map the login to the database they will have access to.
7. Tick the boxes for role membership next to public and db_datareader.
8. Confirm by clicking OK.

Please check steps which you have done earlier.

To view Detail on permission : http://msdn.microsoft.com/en-us/library/ms189121(SQL.100).aspx

http://www.databasejournal.com/features/mssql/article.php/1441261/Understanding-SQL-Server-Roles.htm

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 3

I think both Nag and Ahsan didn't understood my question!!!

Just by giving server role as public and user mapping as db_datareader and public for a user will not show properties of databases in the server. I want to create a user in sql server with minimum permission(with read only) and access the database properties.So which  all permission i should give to achieve this?

Answer 4

The user needs the permission VIEW SERVER STATE and must have been granted access to the database of which he wants to view properties. The user does not need any further permissions in the database.

Largely, all database properties are by default visible to all users, with one exception. Using Profiler I found a big query to retrieve properties, and the culprit is the DMV sys.dm_database_encryption_keys and all that is retrieved is whether the database has an encryption key for TDE. Thus, because of this single column, a plain user cannot retrieve database properties through SSMS.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

I just created a test user which has public server role and created a test user for this test login in a user database with db_reader and public database roles.

I connected with this test login and I was able to open the properties of the database on which it has permissions.

Answer 6

hey keerthi deep.. Are you sure about it?Just by having public server role and db_reader and public roles in user mapping you can access db properties?

Answer 7

Yes Winman. If you have any other SQL instance on which you can test this and see if it works there.
I tested this on SQL Server 2005 instance and SQL Server 2008 R2 instance.

Answer 8

I checked it about 3 instances. In all cases same error came. i gave VIEW SERVER STATE permission.But still i am not able to access database properties.If i login in my admin account of sql server then i am able to see db properties. but i want to create a new user with readonly permission and to access db properties.

Answer 9

what is the error message you are receiving?

Answer 10

This is the error message

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

User 'user3l' does not have permission to run DBCC showfilestats for database 'dbname'. (Microsoft SQL Server, Error: 7983)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.5500&EvtSrc=MSSQLServer&EvtID=7983&LinkId=20476

Answer 11

OK, I was too quick and I should have asked about the version of SQL Server you are using, and particularly the version of SSMS.

Some more research reveals that what you need is not a permission, what you need is the latest service pack. I googled on this undocumented command DBCC SHOWFILESTATS. One hit was a forum thread with an answer I did not really understand - despite that the answer was from myself! However, this blog post sets it straight:
http://sqlish.com/user-public-does-not-have-permission-to-run-dbcc-showfilestats-for-database-testing/
There is a link to a KB article which is for SQL 2008 and not SQL 2008 R2, but I tested on SQL 2008 R2 SP1 and was able to access the database properties with no other permission to the database beyond being granted access.

Please observe: you do not need VIEW SERVER STATE when you use SSMS 2008, R2 or not. I made my tests with SSMS2012, and with SSMS2012, you need VIEW SERVER STATE. In the light of the hotfix, I think this is a bug. But before I submit a bug, I need to install SP1 of SQL 2012 which was released last week so that I don't report something which is already fixed.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 12

Please observe: you do not need VIEW SERVER STATE when you use SSMS 2008, R2 or not. I made my tests with SSMS2012, and with SSMS2012, you need VIEW SERVER STATE. In the light of the hotfix, I think this is a bug. But before I submit a bug, I need to install SP1 of SQL 2012 which was released last week so that I don't report something which is already fixed.

Indeed, this appears to be fixed in SQL 2012 SP1. I am able to access database properties with a plain login on an SQL 2008 instance after installing the service pack.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 13

I am using Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86)   Sep 22 2011 00:28:06   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)  

Microsoft SQL Server Management Studio version is 10.50.1600.1

In service pack 2 also i am getting same error.

Is it because of db_owner permission not given for the database?

In sql server 2012 express i did the same test and when i click on database properties in SSMS i got error as you don't hav permission to access...

Answer 14

Note that you need the service pack for Managment Studio, not for the engine. That is, you need to apply the service pack on your workstation.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 15

Does service pack 3 also has same problem?

Answer 16

So the KB article that I indirectly referred you to said that the problem was fixed in CU7 for some service pack; which service pack I did not pay attention to. However, since I tested the scenario on both SQL 2008 SP3 and SQL 2008 R2 SP1, and the problem did not reproduce, my assumption is that the problem is fixed in the most recent service pack.

Note, though, that there is no SP3 for SQL 2008 R2.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 17

I am using sql server 2008 standard service pack 3. The KB article is Cumulative update package 7 for SQL Server 2008 Service Pack 1. So service pack 3 should include all fixes of service pack 1 right? But still i am getting error in service pack 3. So what should i do?

Answer 18

In SSMS, what does Help->About say about your version of SQL Server Management Studio?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 19

In SSMS i checked in help->About.

Microsoft SQL Server Management Studio 10.50.1600.1
Microsoft Data Access Components (MDAC) 3.85.1132
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 
Microsoft Internet Explorer  6.0.2900.5512
Microsoft .NET Framework 2.0.50727.3053
Operating System 5.1.2600

These are the details i got.How can i check service pack of SSMS?

Answer 20

Microsoft SQL Server Management Studio 10.50.1600.1

This is the RTM version! As I've pointed out a couple of times, you should install the latest service pack to address this problem. And you need to install it on the machine where you run SSMS.

These are the details i got.How can i check service pack of SSMS?

The version numbers for SSMS are the same as for the engine, so if you have an instance of SQL 2008 R2, you can compare version numbers.

This web site has a comprehensive list of SQL Server version numbers:
http://sqlserverbuilds.blogspot.se/

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 21

Yes you are correct.I installed sql server 2008 r2 express service pack2 i.e 10.50.4000 and problem solved.

Now i have installed management studio from http://www.microsoft.com/en-us/download/details.aspx?id=30438 which is 158 MB. Previously installed  management studio was of 154 MB(without service pack). In the link i provided has new setup of SSMS with service pack 2. Now if want to correct the problem then i should install this new setup in all PC's. It takes so much time to install. Can i get only service pack(like service pack for windows etc) so that size will be reduced? 

Answer 22

I'm not sure that I understand the question, but yes you need to installt the Service Pack on all PC where this is a problem. The installation of the service pack takes some time, and in my experience the client parts is what takes the longest time. Thus, if these PCs only have the client and not the server, that does not help that much.

Of course, installing the service pack on all PCs is a tedious work. But Microsoft has solution for this. System Center, previously called Systems Management Server, permits an IT department to push fixes and updates to a large amount of client PCs. Of course, if you don't use System Center today, there is some work to get it going, and it would be an overkill only to push a specific update of SSMS. But depending the size of your organisation, it may be an investment that pays off.

Disclaimer: I'm not a Windows Admin guy myself, so I cannot answer specific questions about System Center.

---

Erland Sommarskog, SQL Server MVP, [e-mail]