September 19, 2013 at 12:02 pm
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:hehe:
September 19, 2013 at 3:36 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 20, 2013 at 12:30 am
I restore backup database on development server, and i want to give permission for new restored database to a existing user on development server.
September 20, 2013 at 1:21 am
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?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 20, 2013 at 4:10 am
Hi,
Want to give read and write permission of new restored database.
Regards
Dharmendra
September 20, 2013 at 4:28 am
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply