Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server User Permission Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 12:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 4:14 AM
Points: 17, Visits: 112

I have Two Servers
1. Production
2. Development

I have prepared a database backup on production server and restore it on development server.

I want to give permission to a existing user on production server for new database.

Whats process i have to follow....

Please tell me step by step because i am new on sql server.


Regards
Dharmendra Kumar
09717765141
Post #1496530
Posted Thursday, September 19, 2013 3:36 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
Your question is not clear. You say that you want to give permission to new user in the prodcution database. But in that case, why do you mention the fact that you copied it to the development server?

Also, are talking about an SQL login or a Windows login?

And what permissions do you want to assign? To permit a user to access a database at all, you do:

USE master
go
CREATE LOGIN [domain\someuser] FROM WINDOWS
go
USE mydb
go
CREATE USER [domain\someuser]

The user will now be able to access the database, but unless you have granted access to public (and you shouldn't), he will not be able to see or modify any data in the database.



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1496616
Posted Friday, September 20, 2013 12:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 4:14 AM
Points: 17, Visits: 112
I restore backup database on development server, and i want to give permission for new restored database to a existing user on development server.
Post #1496721
Posted Friday, September 20, 2013 1:21 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
Dharmendra JKT (9/20/2013)
I restore backup database on development server, and i want to give permission for new restored database to a existing user on development server.


OK. But what permission? Should the user be able to do anything in the database? Only read data? Only access certain tables? Be able to create stored procedures, but not tables?


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1496729
Posted Friday, September 20, 2013 4:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 4:14 AM
Points: 17, Visits: 112
Hi,

Want to give read and write permission of new restored database.

Regards
Dharmendra
Post #1496781
Posted Friday, September 20, 2013 4:28 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
Read and write data, but not being able to alter any objects? In that case do

ALTER ROLE db_datareader ADD MEMBER [domain\user]
ALTER ROLE db_datawriter ADD MEMBER [domain\user]

or, if you are on SQL 2008 or earlier:
EXEC sp_addrolemember db_datareader, 'domain\user'
EXEC sp_addrolemember db_writereader, 'domain\user'

You first need to add the user to the database as per the previous post.

As I typed these commands from memory, I advice you to check the exact syntax in Books Online.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1496792
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse