exclusive - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

exclusive

Author

Topic

db
Starting Member

2 Posts

Posted - 2003-03-27 : 09:03:44

Hi,

what role gives the system administrator the
EXCLUSIVE use of the mssql 7 database?

thanks,
db

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-03-27 : 09:10:47

ALTER DATABASE db SET RESTRICTED_USER

this way only people from the sysadmin,dbcreator or dbo roles can access the server.

(and SET MULTI_USER returns to normal)

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-03-27 : 09:12:20

sigh, SQL 7!!

Note to self: read question!

my reply was for SQL 2K

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-03-27 : 18:57:05

Well you can't do that but you have the choice to make it accessible to either a single user or to dbo only using sp_dboption. See BOL for details.

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-27 : 19:18:42

There isn't really an option just for sa but there is the dbo option. See sp_dboption in BOL as jasper_smith mentioned and pass it the dbo use only option. But all dbo's will get access. If you want just one person, say the sa, to have access to it then pass sp_dboption the single user option.

For example:
USE master
EXEC sp_dboption 'pubs', 'single user', 'TRUE'

Tara

db
Starting Member

2 Posts

Posted - 2003-03-29 : 16:54:50

I use sqlserver 2000 also.
oracle requires a passwd for the database when the dba creates
a new database.
in sql server there is no this security.
in db_options if you don't give/select none role can't even
the windows system admin to have access to the database?
with your following code on sql2000 server i thought you
can do the samething when you assign specific roles in
the db_option???

it is alter database (name of database) set restricted_user(user account) because i get errors on 2k server.

thanks

"ALTER DATABASE db SET RESTRICTED_USER

this way only people from the sysadmin,dbcreator or dbo roles can access the server.

(and SET MULTI_USER returns to normal)"

[/quote]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources