Sql-server – SQL server security

sql server

I have requirement that I need to take backup from production and need to restore it in development.

I have taken all users and permissions from production by generating scripts, but two users are facing an issue in the development copy because they do not have permission to CREATE,ALTER and EXECUTE. When I used the generate script feature to get their permissions from production, they are only db_reader and db_writer. But now they are getting errors like "execute permissions denied in stored procedure". Prior they were able to CREATE ,ALTER, and DROP stored procedures successfully.

How is this possible? My question is will the db_writer role allow a user to CREATE and DROP stored procedures?

Best Answer

My question is db_writer will create ,drop, alter the stored procedure

The answer is NO.

db_datawriter can only do INSERT, UPDATE, DELETE.

There could be a case when some user was the owner of that database, so it had the permissions of db_owner in this database, and after restore the owner was changed, but it cannot be your case as you have two users that now have not EXECUTE permission.

Related Solutions

Sql-server – SQL Server – DBO without Backup

Grant the user SELECT, INSERT, UPDATE, DELETE, EXECUTE and VIEW DEFINITION rights to the database (or the schema). This will grant the user the rights to all the objects within the database (or the schema).

You'll probably also need to give them rights like ALTER ANY OBJECT within the database as well so that they can modify the objects.

SQL Server Error 615 – db_owner Unable to Drop Database

I can guess you have the AutoClose option for database set to True. This is the default behavior when you create a database with Express Editions.

The mentioned error can occur exactly in this case. Actually the complete error message 615 states: "Could not find database ID %d, name '%.*ls'. The database may be offline. Wait a few minutes and try again." ... So it points that database could be closed during dropping.

So, go to DB properties, switch it to False and try again dropping it or use below script before dropping

ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF 
GO

Many point out that is better to have AutoClose set to False. I found this article explaining a bit more about AutoClose: http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases

Small extension of the answer:

-- this works in standard SQL Server Editions, but NOT with Express Editions:
CREATE DATABASE [MyDB]
GO
DROP DATABASE [MyDB]
GO

-- this works in ALL SQL Server Editions
CREATE DATABASE [MyDB]
GO
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF 
GO
DROP DATABASE [MyDB]
GO

Best Answer

Related Solutions

Sql-server – SQL Server – DBO without Backup

SQL Server Error 615 – db_owner Unable to Drop Database

Related Question