Help! our DBA died
-
I wish it was a joke, but our DBA passed away. Till such time we find a new DBA, I am the man. I have this script: exec sp_droprole 'SAM_USER' exec sp_addrole 'SAM_USER' exec sp_droprole 'Manage_Presentation' exec sp_addrole 'Manage_Presentation' grant select , insert,delete , update on Presentation to Manage_Presentation grant select , insert,delete , update on Presentation_Menu_Link to Manage_Presentation grant select , insert,delete , update on PresentationOwner to Manage_Presentation grant select , insert,delete , update on PresentationSlide to Manage_Presentation grant select , insert,delete , update on PresentationSlideOverride to Manage_Presentation grant select , insert,delete , update on PresentationSlideVerticalMenu to Manage_Presentation exec sp_addrole 'Manage_Slides' grant select , delete , update on dbo.Slide to Manage_Slides grant select , delete , update on dbo.Slide_Menu_Link to Manage_Slides grant select , delete , update on dbo.SlideComment to Manage_Slides grant select , delete , update on dbo.SlideFile to Manage_Slides grant select , delete , update on dbo.SlideType to Manage_Slides grant select , delete , update on dbo.SYSFile to Manage_Slides exec sp_addrolemember 'SAM_USER','Manage_Presentation' exec sp_addrolemember 'SAM_USER','Manage_Slides' --create user section of the database exec sp_addlogin @loginame = 'Sameer',@passwd = 'Sameer',@defdb = 'SAM_DEV' exec sp_grantdbaccess 'SameerHanda' exec sp_addrolemember 'SAM_USER','SameerHanda' Well all well and good, but when I signon as Sameer and do the following select * from Presentation, I ge this error: SELECT permission denied on object 'Presentation', database 'SAM_DEV', owner 'dbo'. Well what am I missing. Any help would be greatly appreciated Thanks Sameer PS: RIP Kev Dawg :((
-
I wish it was a joke, but our DBA passed away. Till such time we find a new DBA, I am the man. I have this script: exec sp_droprole 'SAM_USER' exec sp_addrole 'SAM_USER' exec sp_droprole 'Manage_Presentation' exec sp_addrole 'Manage_Presentation' grant select , insert,delete , update on Presentation to Manage_Presentation grant select , insert,delete , update on Presentation_Menu_Link to Manage_Presentation grant select , insert,delete , update on PresentationOwner to Manage_Presentation grant select , insert,delete , update on PresentationSlide to Manage_Presentation grant select , insert,delete , update on PresentationSlideOverride to Manage_Presentation grant select , insert,delete , update on PresentationSlideVerticalMenu to Manage_Presentation exec sp_addrole 'Manage_Slides' grant select , delete , update on dbo.Slide to Manage_Slides grant select , delete , update on dbo.Slide_Menu_Link to Manage_Slides grant select , delete , update on dbo.SlideComment to Manage_Slides grant select , delete , update on dbo.SlideFile to Manage_Slides grant select , delete , update on dbo.SlideType to Manage_Slides grant select , delete , update on dbo.SYSFile to Manage_Slides exec sp_addrolemember 'SAM_USER','Manage_Presentation' exec sp_addrolemember 'SAM_USER','Manage_Slides' --create user section of the database exec sp_addlogin @loginame = 'Sameer',@passwd = 'Sameer',@defdb = 'SAM_DEV' exec sp_grantdbaccess 'SameerHanda' exec sp_addrolemember 'SAM_USER','SameerHanda' Well all well and good, but when I signon as Sameer and do the following select * from Presentation, I ge this error: SELECT permission denied on object 'Presentation', database 'SAM_DEV', owner 'dbo'. Well what am I missing. Any help would be greatly appreciated Thanks Sameer PS: RIP Kev Dawg :((
You've added the Manage_Presentation and Manage_Slides roles to the SAM_USER role. I think you intended it the other way around. You've also specified a different username in the sp_grantdbaccess and sp_addrolemember calls than you did in the sp_addlogin call. I think you simply need to change
exec sp_addrolemember 'SAM_USER','Manage_Presentation'
exec sp_addrolemember 'SAM_USER','Manage_Slides'to
exec sp_addrolemember 'Manage_Presentation','SAM_USER'
exec sp_addrolemember 'Manage_Slides','SAM_USER'Stability. What an interesting concept. -- Chris Maunder
-
You've added the Manage_Presentation and Manage_Slides roles to the SAM_USER role. I think you intended it the other way around. You've also specified a different username in the sp_grantdbaccess and sp_addrolemember calls than you did in the sp_addlogin call. I think you simply need to change
exec sp_addrolemember 'SAM_USER','Manage_Presentation'
exec sp_addrolemember 'SAM_USER','Manage_Slides'to
exec sp_addrolemember 'Manage_Presentation','SAM_USER'
exec sp_addrolemember 'Manage_Slides','SAM_USER'Stability. What an interesting concept. -- Chris Maunder
:laugh:Cool thanks it seems to have worked, I am a c# guy being forced to act as a DBA for the time being. Good experience none the less.
-
:laugh:Cool thanks it seems to have worked, I am a c# guy being forced to act as a DBA for the time being. Good experience none the less.
If your DBA died (sorry to hear that) and you are the acting DBA, I would be more than a little concerned about what happens if your DB dies too. Murphy's Law would suggest that since your DBA died, you are at much higher risk than normal that your SQL Server is going to crash sometime in the near future too. If it does and you are not prepared for it, you could be in for a lot of nasty suprises. If I were in your position, I would run full backups on all the databases and especially the master database immediately. And if you don't know a lot about what you are doing, until you get a new DBA I would advise not worrying so much about the media cost and keep making regular full backups of any databases in active use on a pretty regular basis. Your DBA probably has some kind of transactional backup system implemented. If you don't have his expertise, and your server dies tomorrow, you could really be in a jam.
-
If your DBA died (sorry to hear that) and you are the acting DBA, I would be more than a little concerned about what happens if your DB dies too. Murphy's Law would suggest that since your DBA died, you are at much higher risk than normal that your SQL Server is going to crash sometime in the near future too. If it does and you are not prepared for it, you could be in for a lot of nasty suprises. If I were in your position, I would run full backups on all the databases and especially the master database immediately. And if you don't know a lot about what you are doing, until you get a new DBA I would advise not worrying so much about the media cost and keep making regular full backups of any databases in active use on a pretty regular basis. Your DBA probably has some kind of transactional backup system implemented. If you don't have his expertise, and your server dies tomorrow, you could really be in a jam.
:confused:It's a good thing that I am familair with Oracle DBA stuff. Then again DB's are DB's its just a different way of doing things. I have already made backups of our MDF file, the transaction logs. I am not sure where the redo logs of Sql server are. Working on figuring that out.