SQL Server 2000 - Programmatically Limit Access to Database Owner?

Question

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?

Answer 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

• http://msdn.microsoft.com/en-us/library/ms190249.aspx
• http://msdn.microsoft.com/en-us/library/aa275464(SQL.80).aspx

Answer 2

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?