DataBase Owner
-
How can I change the owner of a Database for all users can access ??? Now, my database has an owner but only this user can access to the tables of the DB. Thanks.
-
You need to grant access to the objects to other users. The dbo gets it by default.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
Your best bet is to set up a database role and grant permissions to that role.
Then make your users members of that role.
Don't be tempted to grant permissions to the "public" role because absolutely everyone is a member of that role and there may come a time when you want to exclude certain accesses from certain users.
-
Databases should be dbo owned. Using query analyzer execute sp_changedbowner 'sa' on the database.
-
quote:
How can I change the owner of a Database for all users can access ??? Now, my database has an owner but only this user can access to the tables of the DB. Thanks. -
quote:
How can I change the owner of a Database for all users can access ??? Now, my database has an owner but only this user can access to the tables of the DB. Thanks.To change the database owner to
"dbo", use this statement:
EXEC sp_changedbowner 'dbo'
You might have to change the owner for the
objects as well.
Good Luck.
-
If you don't use roles then you will be stuck with a lot more upkeep.
Unless I miss my guess all of the objects are already dbo owned, since it sounds like the only person with access is the dbo.
Security is a 'hassle', but essential to running a good shop.
_one_
NT Security -> NT Groups
or
SQL Security -> individuals (I dont use the same login for multiple people when I do this because it can make it difficult to trace who is doing what if the host_name is spoofed - which happens a lot by accident)
_two_
Sysadmin roles etc are static and are server wide. Reserve these for special use.
_three_
created roles inside each db as needed, assigning permissions as needed. If you are just starting, even if you are going to just give them full access, STILL use the role to do it.
_ four _
now assign the NT/SQL logins to the roles in the DBs as needed. This way you only have to set the permissions for a role once instead of for every user. Also, if using NT and your NT security admin deletes and re-adds an account the SID is different. If you dont use roles you'll have to go putz with all of the permissions again. An added layer of protection against NT security admins making any changes in general actually.
-
You dont want to change the owner to grant access for others. You want to add other users to the database users list.
do the following:
-expand database in question using Enterprise manager
-right click over users
-select New Database User...
-In the Login Name Drop Down choose <new>
-in Name select the ... and choose the NT user or group you want to grant access to
-grant no server roles
-grant only access to the database in question
----------
This is only a starting point to get users in the database. You need to create a security plan for your server. Write it down!
Good Luck
-Isaiah
-Isaiah -
quote:
How can I change the owner of a Database for all users can access ??? Now, my database has an owner but only this user can access to the tables of the DB. Thanks.Just use a system Stored Procedure called SP_ChangeObjectOwner 'OldUser.ObjectName','NewUser' best regards
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply