July 1, 2007 at 1:56 pm
Currently I have all my databases on sql server 2005 instance, I have build a new server with sql server 2005 with SP2 64 bit std edition. I want to move databases to new server but I have few question.
I have many SP in each databases. After I move my restore the database to new server, how can I give permission to SP.
Whats the best practice, I want to create a dedicated user for each database and give permission to read, write, delete, update database and execute SP.
July 1, 2007 at 11:53 pm
Hi
I think u can script logins,users and user permissions and run the script on the new server.
"Keep Trying"
July 2, 2007 at 1:25 am
Script out the logins and the use that to create the logins there in the destination server. Then you can use the sp_change_users_login 'auto_fix','username' proceudre to sync the logins in the new server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 2, 2007 at 7:14 am
i just did this
set up new machine
restore db's
transfer logins and sp_change_users_login
set up a temp server with empty databases prior to the move and transfer all jobs. then transfer all jobs to new server
July 2, 2007 at 7:42 am
Thanks everyone for the reply
I am going to create new users name with new security model. I am not trafering any user from old server as security was not desing right.
Help me with this question:
Whats the best practice, I want to create a dedicated user for each database and give permission to read, write, delete, update database and execute SP.
July 2, 2007 at 9:37 am
Hi Patel,
I think u can go ahead with db_datareader and db_datawriter roles for the user you create in the db.
[font="Verdana"]- Deepak[/font]
July 2, 2007 at 9:44 am
if I give user datareader and datawriter role, will they be able to perform insert, update, delete, select and exec sp with out manually giving exec permission on each SP?
Thanks for you help
July 2, 2007 at 11:10 pm
bhavin,
if you give datareader/writer role they will be able to perform insert, update,select and delete but they will not have execute permissions in SPs or functions. Since you have posted this in 2005 forums i assume your server is 2005 and let you know that you can grant execute to SPs in single statement in 2005 as
grant execute any to username.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 3, 2007 at 9:16 am
Hi sugesh,
grant execute any to sqluser will grant execute permission on all sp for that particular user?
Thanks
July 4, 2007 at 7:25 am
Yes grant execute any to username will grant execute permission on all SPs to that user this is a new feature in SQL 2005.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 5, 2007 at 7:24 am
I tried
grant execute any to username and it did not work?
will this command give execute permission to all SP on all databases or just one database?
July 5, 2007 at 8:12 am
Give permissions to SPs in that database and not in all databases. Might be the syntax i have given is wrong check that and fire the right syntax.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 6, 2007 at 12:51 am
You can refer this post
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=359&messageid=366252
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply