1

How would I programmatically go about limiting the access of a database in SQL Server 2000 to the database owner for that database? Example... if I right-click "Properties" on the "Northwind" database in Enterprise Manager, the Owner is listed as sa. How would I limit access for this database to just the sa login?

Donut
  • 110,061
  • 20
  • 134
  • 146

2 Answers2

1

You could set the database to RESTRICTED_USER availability.

ALTER DATABASE MyDatabase SET RESTRICTED_USER

-- OR --

ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
Scott Ivey
  • 40,768
  • 21
  • 80
  • 118
1

Some thoughts:

  • You can neither deny not restrict the sa login at all, anywhere in a SQL Server Instance
  • Do not use "sa" day to day
  • It makes more sense to limit to the members of the db_owner database role, per database (SET RESTRICTED_USER above)
  • The database owner id is fairly random: sa only owns this because sa created it, or ownership was changed to sa

Other than that, what is the reasoning behind your request?

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    I'm working on an application that will run a series of scripts to modify/upgrade the schema of a given database, and need to allow `sa` access for this program... however, I don't want other users/programs connecting to the database while these schema modifications are being performed. I think the `SET RESTRICTED_USER` answer is what I'm looking for. Thanks for your helpful comments as well, the more I know about the `sa` login the better. – Donut Jul 30 '09 at 12:39
  • Ah OK, makes sense. SET RESTRICTED_USER is what you need then. – gbn Jul 30 '09 at 12:44