CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008authenticationsql-server-2012
John Bustos
John Bustos

Reputation: 19574

SQL Server - Create a new database for a user and ONLY give them access / view rights to that one DB

I have admin rights on a SQL Server 2012 Server and have a user that wants me to create a "throw away" database for them on the server. Basically, I'm looking to:

  1. Create this database
  2. Give the user full access / rights TO ONLY THAT DATABASE
  3. Have them see it and its schema - but not any other DBs - in the SSMS Object Explorer

I've found quite a few answers around online and the one that got me closest was this one: https://stackoverflow.com/a/15400392/1693085

Basically giving me these lines of SQL to execute:

--Step 1: (create a new user)
create LOGIN hello WITH PASSWORD='foo', CHECK_POLICY = OFF;


-- Step 2:(deny view to any database)
USE master;
GO
DENY VIEW ANY DATABASE TO hello; 


 -- step 3 (then authorized the user for that specific database , you have to use the  master by doing use master as below)
USE master;
GO
ALTER AUTHORIZATION ON DATABASE::yourDB TO hello;
GO

However, when I then log in as this user and right click on a created table, I get the following error repeated dozens of times:

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

------------------------------
ADDITIONAL INFORMATION:

The EXECUTE permission was denied on the object 'xp_instance_regread', database 'mssqlsystemresource', schema 'sys'.

And, if I ignore it and try and delete the table anyways, I get basically the same error again.

All I basically want to do is create a user and ensure they have no more access than that one database, but have full access on that one...

Am I doing something wrong / What should I change??

Thanks!!!

Upvotes: 2

Views: 2927

Answers (1)

Dave.Gugg
Dave.Gugg

Reputation: 6781

You can get around this by granting the user permission to the stored procedure:

USE master
GO

GRANT EXEC ON OBJECT::master.dbo.xp_instance_regread TO hello
GO

Upvotes: 1

Related Questions