How to Grant Access to a Snap Shot

If you try to directly grant access to a snap shot you will get the below error stating that the database is in read only mode.


Msg 3906, Level 16, State 1, Line 1
Failed to update database "" because the database is read-only.
Msg 3906, Level 16, State 1, Procedure sp_MSadduser_implicit_ntlogin, Line 39
Failed to update database "" because the database is read-only


The reason is that the snapshots are in read only mode due to which we cannot make changes to add or remove a user.


If its a windows user then create a login in both the primary and mirror server. Then grant access to this login to the primary database, which will be replicated to the mirror.

If its a SQL Server login then its a bit tricky as the SIDs come into picture. You have to create a login in the primary then script it out with the SID using sp_help_revlogin script and then run the script in the mirror. Then grant the required access in the primary, which inturn will get replicated and the required user will have read access in the mirror and the snap shot.


Thats it for the day folks. Hope to I bump into some similar kind of problem and find a solution and waste google's page by blogging it here again. Until then its me signing off from you. Happy administrating & happy sunday cya..





With Smiles
Santhosh

Removing Identity Column from a large table

To disable an identity column we basically rely on management studio, which intern create temp table transfer the data back and forth for us. But if the table is bit huge with millions of rows, you will get a time out error. Normally most of the solution given by people would be to create a temp table with no identity column transfer the rows into it and then rename the table.



The catch in this is , there is a very bright chance of you missing some relationship as you have to create them manually. So the innovative way invented by meeeeeeeeee is ;) (tanta da tanta da doiiiiii hehe back ground music) is the below


1. Create a teamporary table as shown below


select * into temp from main

2. select count(*) from temp


select count(*) from main

check if the rows are equal just in case




3. Truncate table main

Remove the identity property from the GUI.

4, ALTER TABLE main

NOCHECK CONSTRAINT ALL


INSERT into main 

select * from temp


ALTER TABLE main

CHECK CONSTRAINT ALL






5. select count(*) from main


select count(*) from temp

check if the rows are equal just in case




6. Drop table temp  -- Dont put the main table name then ur inviting a disaster


Thats it for the day folks. Hope to see you all soon with some thing uninteresting ;).Until then its me signing off



With Smiles
Santhosh