<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Why I need to assign Grant/Deny Permission...
Why I need to assign Grant/Deny Permission after every database restore ?
Rate Topic
Display Mode
Topic Options
Author
Message
maida_rh
maida_rh
Posted Wednesday, January 30, 2013 9:43 PM
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 11:58 PM
Points: 42,
Visits: 97
I need to restrict user which was achieved by grant and deny permissions using TSQL in sql server 2008 R2 successfully.
Now i need to update database,For know ,I am using backup/restore for updation of database.
But when ever i did it ,User grant/Deny were needed to assign .
Is their any thing that i used to grant/deny permission and no need to assign then on every restore/backup ???
Kindly let me know
Post #1413839
Bhuvnesh
Bhuvnesh
Posted Thursday, January 31, 2013 12:06 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
maida_rh (1/30/2013)
I need to restrict user which was achieved by grant and deny permissions using TSQL in sql server 2008 R2 successfully.
Now i need to update database,For know ,I am using backup/restore for updation of database.
But when ever i did it ,User grant/Deny were needed to assign .
Is their any thing that i used to grant/deny permission and no need to assign then on every restore/backup ???
Kindly let me know
i think you are refering here orphan login fix . right ?
IF you are using same copy of database again and again, then restore the backup , do the login fix and then take the backup and use it everytime.
if you are using different and latest backup copy then you have to do the login fix everytime.
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1413858
maida_rh
maida_rh
Posted Thursday, January 31, 2013 12:38 AM
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 11:58 PM
Points: 42,
Visits: 97
I am using sql server authentication and user i assigned permission also have login id and pass,I googled the term "orphan login and got this :
[url=http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm][/url]
what do i need to do as i need the restore to commit the change to a single table structure + data .
I tryed the import but got error:
Violation of primary key constraints 'userid' .can not insert duplicate key in object 'usertb'
Kindly let me know and thanks
Post #1413866
Bhuvnesh
Bhuvnesh
Posted Thursday, January 31, 2013 1:05 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
maida_rh (1/31/2013)
what do i need to do as i need the restore to commit the change to a single table structure + data .
I tryed the import but got error:
Violation of primary key constraints 'userid' .can not insert duplicate key in object 'usertb'
How you are restoring ? through backup ?
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1413872
maida_rh
maida_rh
Posted Thursday, January 31, 2013 1:57 AM
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 11:58 PM
Points: 42,
Visits: 97
yes,through backup file
Post #1413906
Bhuvnesh
Bhuvnesh
Posted Thursday, January 31, 2013 2:31 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
maida_rh (1/31/2013)
yes,through backup file
then how come PK violation ? restore backup do data copy of exact the content in backup , there cant be PK violation
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1413925
maida_rh
maida_rh
Posted Thursday, January 31, 2013 3:14 AM
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 11:58 PM
Points: 42,
Visits: 97
Pk_violation when i tried to import data from current updated source database to destination database.
How can I resolve orphan login as ,already assiged a login password to that user ????
Post #1413954
Bhuvnesh
Bhuvnesh
Posted Thursday, January 31, 2013 3:32 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
maida_rh (1/31/2013)
Pk_violation when i tried to import data from current updated source database to
destination database.
still you are not clear here , are you populating tables on destination side which already contain data then there is possibilty of PK violation. if possible(Be very very careful) that you can afford then truncate the table at dest. side and then populate them.
maida_rh (1/31/2013)
How can I resolve orphan login as ,already assiged a login password to that user ????
ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa
GO
see this
http://msdn.microsoft.com/en-us/library/ms187359.aspx
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1413961
maida_rh
maida_rh
Posted Thursday, January 31, 2013 3:43 AM
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 11:58 PM
Points: 42,
Visits: 97
I tryed TRUNCATE on table but still the same error of PK violation during import/Export.
What does AuTHORIZATION do ?If I assigned to sa ?
Post #1413966
Bhuvnesh
Bhuvnesh
Posted Thursday, January 31, 2013 3:52 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
maida_rh (1/31/2013)
I tryed TRUNCATE on table but still the same error of PK violation during import/Export.
So it means your source table contains duplicate data ? do you have same PK on source table too as you have on dest table ?
maida_rh (1/31/2013)
What does AuTHORIZATION do ?If I assigned to sa ?
See the link i posted above
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1413973
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
