Re: Will anyone know this?
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 07/19/03
- Next message: Dan: "Find out the commands executed"
- Previous message: Ryan LaNeve: "Re: Encryption to and ADP"
- In reply to: Ben Taylor: "Re: Will anyone know this?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Sat, 19 Jul 2003 12:40:10 -0500
You can determine if your login is the database owner with sp_helpdb.
If your login isn't listed as the owner, you are probably a member of
the db_owner fixed database role instead. You can execute
sp_helprolemember 'db_owner' to see if this is the case.
The only required user in a SQL Server database is dbo and this special
user is mapped to the login specified as the database owner. The dbo
user has full control over the database and can manipulate objects in
any schema in the database. Members of the db_owner role have similar
permissions to the dbo user except that their default schema is their
user name instead of dbo. Consequently, these users must qualify the
object name with the desired owner in order to create objects owned by
other users.
As Andrew mentioned, dbo object ownership is a best practice in SQL
Server unless you have a specific reason to do otherwise. This
eliminates a number of issues with transferring schema to other
databases or servers and facilitates unbroken ownership chains. When an
existing object is referenced with an unqualified name, SQL Server first
checks for an object owned by the current user and uses the dbo-owned
object only if the user-owned object does no exist. This behavior is
regardless of role membership. One of the nice things about having all
objects owned by dbo is that the referenced object is unambiguous
regardless of whether or not the object name is qualified.
BTW, the reason for the SETUSER in the generated scripts for textual
objects (stored procedure, views, triggers and functions) is to ensure
unambiguous behavior in case the object text does not contain the owner.
If you need to script non-dbo objects and the object names are qualified
in the object text, you might consider using SQLDMO directly from your
VB program rather than using EM. Sample VBScript below.
Option Explicit
Dim oFSO, oFile
Dim oSQLServer, oDatabase, oStoredProcedure
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.OpenTextFile("C:\Scripts\MyProcs.sql", 2, True)
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect("MyServer")
Set oDatabase = oSQLServer.Databases("MyDatabase")
For Each oStoredProcedure In oDatabase.StoredProcedures
oFile.WriteLine oStoredProcedure.Script
oFile.WriteLine "GO"
Next
oFile.Close
-- Hope this helps. Dan Guzman SQL Server MVP ----------------------- SQL FAQ links (courtesy Neil Pike): http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800 http://www.sqlserverfaq.com http://www.mssqlserver.com/faq ----------------------- "Ben Taylor" <ben_taylor_g1@yahoo.co.uk> wrote in message news:0ab401c34d30$4f0d8180$a601280a@phx.gbl... > That's right, they're not dbo owned they're taylorb owned - > but I am set up to be the 'dbowner' - there's intense > confusion here! Am I the database owner, or is the sa > account the database owner, and what is the difference > between 'dbo' and 'dbowner'? > > It's only ever going to be me running the scripts, or if > it is someone else, it's likely to be because they've > taken over the database and in that case they will have > been made the owner of it and will be running it under > their login, so setuser will still be inappropriate. > I understand why it's doing this, but presumably there > isn't any way to decide for myself and turn it off, or do > I have to manually get rid of it? > > I can't see what its actual purpose is, because if someone > else wanted to create the tables 'as me', wouldn't they > need to know my password, and if not isn't this an un- > security conscious function you could use to get round > user-restrictions? > > Thanks > Ben > > > >-----Original Message----- > >I assume this is adding thisinto the script because you > have objects that > >are not owned by dbo and the SETUSER is impersonating > that user during the > >creation of those objects. If you remove it you run the > risk of the > >objects not being created under the proper user. If you > do remove it make > >sure all the objects are preferenced with the proper > owners. > > > >-- > > > >Andrew J. Kelly > >SQL Server MVP > > > > > >"Ben Taylor" <ben_taylor_g1@yahoo.co.uk> wrote in message > >news:061401c34cfe$38d1e5f0$a601280a@phx.gbl... > >> Hi, > >> Does anybody know how to get enterprise manager to stop > >> putting lines like setuser('taylorb') in the output .sql > >> file when you ask it to script a whole load of stored > >> procedures? > >> I had even created a VB program to remove these lines > but > >> it isn't working properly now, and I thought it would be > >> the sort of thing you could turn off but not in an > obvious > >> way. It doesn't work when I'm trying to set the database > >> up on another server for example because there's some > >> security issue with setuser (I'm not an administrator > but > >> am the database owner) - I just want to remove setuser > >> calls. > >> Anybody know? > > > > > >. > >
- Next message: Dan: "Find out the commands executed"
- Previous message: Ryan LaNeve: "Re: Encryption to and ADP"
- In reply to: Ben Taylor: "Re: Will anyone know this?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
