DB Permissions

• Allen B

  SSC-Addicted

  Points: 473

  More actions

  September 21, 2007 at 10:10 am

  #70212

  First off, I'm not a DBA, just a lowly developer.  We're a small company and we don't have an offical dba so we muddle thru.

  We have a third party app that installed a SQL Server DB on SQL Server 2000 machine.  I copied the DB over for testing purposes.  The db owner is sa.   The table owner is smdb.  I have a stored procedure owned by dbo (it was owned by smdb but I need to debug and figured it was easier to set the owner to dbo).  The trouble I'm having is that when I debug, the one table I need is owned by smdb.  If I don't prefix it with smdb. I can't see it.  When I do prefix it, it runs but I can't step thru it.  I've tried giving permissions to dbo to it but it doesn't seem to matter.  I've given every user/role every permission and still I can't even query the table in query analyzer with sa login.  I'm sure this is simple for an actual dba - what am I doing wrong?

  Thanks  

  Thanks

• Lowell

  SSC Guru

  Points: 323518

  More actions

  September 21, 2007 at 10:22 am

  #735307

  you want to change all the objects back to smdb;

  --find everything NOT owned by dbo, and create the sqls to change them to dbo as owner:

  select 'EXEC sp_changeobjectowner "' + u.name + '.' + o.name + '","dbo"' from sysobjects o, sysusers u

   where type = 'u'

   and u.uid = o.uid and u.uid <> 1

  --opposite logic: find stuff owned by dbo, and  create the sqls to change them to smdbas owner:

  select 'EXEC sp_changeobjectowner "' + u.name + '.' + o.name + '","smdb"' from sysobjects o, sysusers u

   where type = 'u'

   and u.uid = o.uid and u.uid = 1

  sample results:

  EXEC sp_changeobjectowner "dbo.INFFOREIGNKEYTBL","smdb"

  EXEC sp_changeobjectowner "dbo.TMPTBINCDET","smdb"

  EXEC sp_changeobjectowner "dbo.TBTENTST","smdb"

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Allen B

  SSC-Addicted

  Points: 473

  More actions

  September 21, 2007 at 10:32 am

  #735311

  After I change things back, how do I get the permissons for degging on my machine in Query Analyzer.  This is what started all this.

   

  Thanks

  Thanks