Re: Complete Neophyte Question(s)
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 29 Jun 2007 22:02:21 +0000 (UTC)
James (minorkeys@xxxxxxxxx) writes:
I have inherited a SQL Server (2005) from an outgoing DBA and while I'm
familiar with databases from a data structure/manipulation standpoint, the
permissions/security model in SQL Server 2005 has baffled me thus far.
From having been very simple-minded in SQL 4.x, it is now quite sophisticated.
As far as I can tell, our previous DBA create a role which is called
SP_Exec. I know that this role has permissions defined somehow, but I'll > be damned if I can figure it out.
In Object Explorer, Databases->yourdb->Security->Roles->Database Roles.
Find the role of interest, and click Properties in the context menu.
Go to the Seucrables tab.
Normally, you grant permissions to roles, and then add users to the
roles. If you were to grant rights to users directly, it would be more
difficult to managed.
Schemas are also slightly confusing, but I imagine that's just a way of
logically grouping sets of objects.
Right. There are some security features related to schemas. If you add
an object to a schema owned by someone else, the schema owner becomes
the object that you created. It is also possible to grant permissions
on a schmea, which implies that you get permissions to all existing
and future objects in the schema to which the permissions apply.
At first glance it seems everything is utilizing the a schema called
dbo. One of our users was having trouble until I went into the schema
and added "View Definition" as a permission.
In SQL 2005, users are only permitted to see objects they have permission
to. This is a change from SQL 2000 where the metadata was visible to
all users.
Can anyone point me in the direction of a document that explains this in
English? Perhaps the majority of my hang-ups are with Management Studio
and not the actual structure of the overall permissions model. ANY help
would be appreciated.
The normal starting point would be
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808659449c5.htm
in Books Online, but admittedly Books Online is surprisingly thin on
some of the permission topics.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Complete Neophyte Question(s)
- From: James
- Complete Neophyte Question(s)
- Prev by Date: Re: Complete Neophyte Question(s)
- Previous by thread: Re: Complete Neophyte Question(s)
- Next by thread: Re: SQL account password not valid after rebuild
- Index(es):
Relevant Pages
|
|
