Question about create database,login,user,schema and grant permissions.

Hi,

I created a database,login,user and schema like belows.

---

-- 2. create database
CREATE DATABASE MyTempDatabase;

-- 3. create login
CREATE LOGIN MyTempLogin WITH PASSWORD = '#mytemplogin$',
DEFAULT_DATABASE = MyTempDatabase,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;

--
USE MyTempDatabase;

-- 4. create user
CREATE USER MyTempLogin FROM LOGIN MyTempLogin WITH DEFAULT_SCHEMA = MyTempSchema;

-- 5. create schema
CREATE SCHEMA MyTempSchema AUTHORIZATION MyTempLogin;

---

The created user,MyTempLogin, must have permissions that can create tables,drop tables,select,insert,delete,update and bulk insert.

How can I grant permissions to the user?(or schema?)

I failed to grant by T-SQL query.

Additionally, what is purppose of the ROLE? Should I create or use it?

I'm confusing in security concept(login,user,schema,role).

Thanks.

i will give u a capsule form of all these database objects

Login : SQL Server uses Two level Security architecture. Server level and Database Level. To access database you should have server level access. But if you have server level access it does not mean that you have database level access. So , Login is nothing but the Server Level Security. User is Database Level Security. If you create a login , you should add that login to a database as Database User, to have access to that database.

Role : is just like Groups in Windows. It is to group users for better admistration so that you can grant permission to the role rather than granting to each user. i think you must use this.

Schema : this is a new concept in sql 2005. This basically groups the objects or it is a container. In earlier versions of sql server , objects were owned by user and both were tightlty coupled. So if you want to drop a user then you need to transfer the objects owned by the user to any other user. In sql server 2005 users are not the owner of the objects but schema is the object owner.

i think Books online is the best resource to uderstand these objects. read and practice

Madhu

|||

To allow the user to create and delete tables make them a member of the ddl_admin role within the MyTempDatabase.

Code Snippet

Use MyTempDatabase

GO

exec sp_addrolemember @.rolename='ddl_admin', @.membername='MyTempLogin'

GO

To allow the user to bulk insert data from a text file they need to be a member of the Buk Insert Fixed Server Role. This is a server wide setting which simply allows them to load data from a text file.

Code Snippet

exec sp_addsrvrolemember @.loginame='MyTempLogin', @.rolename='bulkadmin'

go

Roles are used to make granting permissions to a group of database users easier. Say you have 5 users who all need the same permissions. Instead of setting up there permissions on a user by user basis, you would setup a role and make all the users members of the role. You can then grant the role what ever rights the users need and the users will inheriate those rights from the role. And users can be in more than one role. So if two of your five users need additional rights that the other three don't need you can create a second role, put in the two users and grant that role the extra rights. When this comes into play is when you add a sixth user. You now don't need to remember what rights they need, you simply drop them into the role and they will have the rights.

When using database roles any right which can be granted within a database can be granted to a role.

Question about "Object permissions" & "Statement permissions"

I'm trying to find the answer on question about "Object permissions" &
"Statement permissions".
I read some books and received the discrepant (contradictory) information.
Can you help me to find the truth?
what is the question then?
sql server version would be useful as well. and the description of your
problem/case, for example.
Thanks, Liliya
"RedFox" wrote:

> I'm trying to find the answer on question about "Object permissions" &
> "Statement permissions".
> I read some books and received the discrepant (contradictory) information.
> Can you help me to find the truth?
|||The truth about what exactly? Can you be more specific?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"RedFox" <redfox_net@.ukr.net> wrote in message
news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
> I'm trying to find the answer on question about "Object permissions" &
> "Statement permissions".
> I read some books and received the discrepant (contradictory) information.
> Can you help me to find the truth?
|||RedFox
Object permissions means ( as I understand it) that you do not have any
access on. For instance you table called "Product" and cannot
INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
Statement permissions is a little bit difference as you may have SELECT
statement permission to read the data, however , you cannot modify any data
(UPDATE permission)
"RedFox" <redfox_net@.ukr.net> wrote in message
news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
> I'm trying to find the answer on question about "Object permissions" &
> "Statement permissions".
> I read some books and received the discrepant (contradictory) information.
> Can you help me to find the truth?
|||your question is...? ask one
Thanks, Liliya
"Uri Dimant" wrote:

> RedFox
> Object permissions means ( as I understand it) that you do not have any
> access on. For instance you table called "Product" and cannot
> INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
> Statement permissions is a little bit difference as you may have SELECT
> statement permission to read the data, however , you cannot modify any data
> (UPDATE permission)
>
>
> "RedFox" <redfox_net@.ukr.net> wrote in message
> news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
>
>
|||Dear All.
Excuse me for a shot message. Additional:
- Book "Microsoft? SQL Server 2005 Implementation and Maintenance
Study Guide (Exam 70–431)" by Joseph L. Jorden Dandy Weyn
In this book I found the descriptions:
- "Statement permissions have nothing to do with the actual data; they allow
users to create the structure that holds the data."
- "Once the structure exists to hold the data, you need to give users
permission to start working with the data in the databases, which is
accomplished by granting object permissions to your users."
Is these terms correct for SQL 2005 in this context?
"Uri Dimant" wrote:

> RedFox
> Object permissions means ( as I understand it) that you do not have any
> access on. For instance you table called "Product" and cannot
> INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
> Statement permissions is a little bit difference as you may have SELECT
> statement permission to read the data, however , you cannot modify any data
> (UPDATE permission)
>
>
> "RedFox" <redfox_net@.ukr.net> wrote in message
> news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
>
>
|||"RedFox" wrote:

> Dear All.
> Excuse me for a shot message. Additional:
> - Book "Microsoft? SQL Server 2005 Implementation and Maintenance
> Study Guide (Exam 70–431)" by Joseph L. Jorden Dandy Weyn
> In this book I found the descriptions:
> - "Statement permissions have nothing to do with the actual data; they allow
> users to create the structure that holds the data."
not really correct without the context. for example this particular
statement in the context as is is not applicable at all to statement
permissions
select, delete, update, insert, because they are not ddl

> - "Once the structure exists to hold the data, you need to give users
> permission to start working with the data in the databases, which is
> accomplished by granting object permissions to your users."
that is not correct in the context given also, because create database
statement permission for example can not deal with any data yet, they just do
not exist, if other 'create' statement permissions have anything to do with
the data is outside of the context of the quotation above as well, they are
for ddl after all.

> Is these terms correct for SQL 2005 in this context?
I would not say the statements are correct as given, however in its own
context of the book they may be.
If you want to find your what permissions the user has, then that is
sp_helprotect or permission function.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/81625a56-b160-4424-91c5-1ce8b259a8e6.htm
or
http://msdn2.microsoft.com/en-us/library/ms186915.aspx
can find your own permissions as well fn_my_permissions.
statement permission can be granted, revoked, denied and audited. Can a user
XYZ do action ABC on the object Q123 is defined by the chain of ownership
(what rights are granted, denied, revoked through what and on what).
Thanks, Liliya
[vbcol=seagreen]
> "Uri Dimant" wrote:

Question about "Object permissions" & "Statement permissions"

I'm trying to find the answer on question about "Object permissions" &
"Statement permissions".
I read some books and received the discrepant (contradictory) information.
Can you help me to find the truth?what is the question then?
sql server version would be useful as well. and the description of your
problem/case, for example.
--
Thanks, Liliya
"RedFox" wrote:
> I'm trying to find the answer on question about "Object permissions" &
> "Statement permissions".
> I read some books and received the discrepant (contradictory) information.
> Can you help me to find the truth?|||The truth about what exactly? Can you be more specific?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"RedFox" <redfox_net@.ukr.net> wrote in message
news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
> I'm trying to find the answer on question about "Object permissions" &
> "Statement permissions".
> I read some books and received the discrepant (contradictory) information.
> Can you help me to find the truth?|||RedFox
Object permissions means ( as I understand it) that you do not have any
access on. For instance you table called "Product" and cannot
INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
Statement permissions is a little bit difference as you may have SELECT
statement permission to read the data, however , you cannot modify any data
(UPDATE permission)
"RedFox" <redfox_net@.ukr.net> wrote in message
news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
> I'm trying to find the answer on question about "Object permissions" &
> "Statement permissions".
> I read some books and received the discrepant (contradictory) information.
> Can you help me to find the truth?|||your question is...? ask one :)
--
Thanks, Liliya
"Uri Dimant" wrote:
> RedFox
> Object permissions means ( as I understand it) that you do not have any
> access on. For instance you table called "Product" and cannot
> INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
> Statement permissions is a little bit difference as you may have SELECT
> statement permission to read the data, however , you cannot modify any data
> (UPDATE permission)
>
>
> "RedFox" <redfox_net@.ukr.net> wrote in message
> news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
> > I'm trying to find the answer on question about "Object permissions" &
> > "Statement permissions".
> > I read some books and received the discrepant (contradictory) information.
> > Can you help me to find the truth?
>
>|||Dear All.
Excuse me for a shot message. Additional:
- Book "Microsoftâ?¢ SQL Server 2005 Implementation and Maintenance
Study Guide (Exam 70â'431)" by Joseph L. Jorden Dandy Weyn
In this book I found the descriptions:
- "Statement permissions have nothing to do with the actual data; they allow
users to create the structure that holds the data."
- "Once the structure exists to hold the data, you need to give users
permission to start working with the data in the databases, which is
accomplished by granting object permissions to your users."
Is these terms correct for SQL 2005 in this context?
"Uri Dimant" wrote:
> RedFox
> Object permissions means ( as I understand it) that you do not have any
> access on. For instance you table called "Product" and cannot
> INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
> Statement permissions is a little bit difference as you may have SELECT
> statement permission to read the data, however , you cannot modify any data
> (UPDATE permission)
>
>
> "RedFox" <redfox_net@.ukr.net> wrote in message
> news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
> > I'm trying to find the answer on question about "Object permissions" &
> > "Statement permissions".
> > I read some books and received the discrepant (contradictory) information.
> > Can you help me to find the truth?
>
>|||This is more about terminology than about technicalities. In earlier versions of SQL Server, use
used to table about "object permissions", for example:
GRANT SELECT ON Customers TO RedFox
We also talked about "statement permissions", such as:
GRANT CREATE TABLE TO RedFox
GRANT BACKUP DATABASE TO RedFox
With SQL Server 2005, MS tries to align terminology with what we have in Windows (and AD etc). So,
we talk about principals (user, login, role) and securables (table, procedure, view etc). A
securable lives in some specific scope (a table lives in a schema, an assembly lives in a database,
an endpoiint lives in a server). So, we have now for instance "Database level permissions", such as:
GRANT CREATE TABLE TO RedFox
Above doesn't apply to some securable, and is what we used to call "statement permission".
We also have, for instance "Database Level Securable Permissions", such as
GRANT EXECUTE ON ASEMBLY::myAssembly TO RedFox
We also have for instance "Schema Level Securable Permissions", such as:
GRANT SELECT ON Customers TO RedFox
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RedFox" <redfox_net@.ukr.net> wrote in message
news:1E0081B4-B193-48E0-8F13-57E866F9A736@.microsoft.com...
> Dear All.
> Excuse me for a shot message. Additional:
> - Book "Microsoftâ?¢ SQL Server 2005 Implementation and Maintenance
> Study Guide (Exam 70â'431)" by Joseph L. Jorden Dandy Weyn
> In this book I found the descriptions:
> - "Statement permissions have nothing to do with the actual data; they allow
> users to create the structure that holds the data."
> - "Once the structure exists to hold the data, you need to give users
> permission to start working with the data in the databases, which is
> accomplished by granting object permissions to your users."
> Is these terms correct for SQL 2005 in this context?
> "Uri Dimant" wrote:
>> RedFox
>> Object permissions means ( as I understand it) that you do not have any
>> access on. For instance you table called "Product" and cannot
>> INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
>> Statement permissions is a little bit difference as you may have SELECT
>> statement permission to read the data, however , you cannot modify any data
>> (UPDATE permission)
>>
>>
>> "RedFox" <redfox_net@.ukr.net> wrote in message
>> news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
>> > I'm trying to find the answer on question about "Object permissions" &
>> > "Statement permissions".
>> > I read some books and received the discrepant (contradictory) information.
>> > Can you help me to find the truth?
>>|||"RedFox" wrote:
> Dear All.
> Excuse me for a shot message. Additional:
> - Book "Microsoftâ?¢ SQL Server 2005 Implementation and Maintenance
> Study Guide (Exam 70â'431)" by Joseph L. Jorden Dandy Weyn
> In this book I found the descriptions:
> - "Statement permissions have nothing to do with the actual data; they allow
> users to create the structure that holds the data."
not really correct without the context. for example this particular
statement in the context as is is not applicable at all to statement
permissions
select, delete, update, insert, because they are not ddl
> - "Once the structure exists to hold the data, you need to give users
> permission to start working with the data in the databases, which is
> accomplished by granting object permissions to your users."
that is not correct in the context given also, because create database
statement permission for example can not deal with any data yet, they just do
not exist, if other 'create' statement permissions have anything to do with
the data is outside of the context of the quotation above as well, they are
for ddl after all.
> Is these terms correct for SQL 2005 in this context?
I would not say the statements are correct as given, however in its own
context of the book they may be.
If you want to find your what permissions the user has, then that is
sp_helprotect or permission function
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/81625a56-b160-4424-91c5-1ce8b259a8e6.htm
or
http://msdn2.microsoft.com/en-us/library/ms186915.aspx
can find your own permissions as well fn_my_permissions.
statement permission can be granted, revoked, denied and audited. Can a user
XYZ do action ABC on the object Q123 is defined by the chain of ownership
(what rights are granted, denied, revoked through what and on what).
--
Thanks, Liliya
> "Uri Dimant" wrote:
> > RedFox
> > Object permissions means ( as I understand it) that you do not have any
> > access on. For instance you table called "Product" and cannot
> > INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
> > Statement permissions is a little bit difference as you may have SELECT
> > statement permission to read the data, however , you cannot modify any data
> > (UPDATE permission)
> >
> >
> >
> >
> > "RedFox" <redfox_net@.ukr.net> wrote in message
> > news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
> > > I'm trying to find the answer on question about "Object permissions" &
> > > "Statement permissions".
> > > I read some books and received the discrepant (contradictory) information.
> > > Can you help me to find the truth?
> >
> >
> >

Question

Hi
Can sql server jobs we viewed by account without sa access? If so what
permissions need to be granted?Answered here:
http://groups.google.de/group/microsoft.public.sqlserver.security/browse_thread/thread/1f7ed5fedba0a853/3c3ff63979b36397?q=jens+S%C3%BC%C3%9Fmeyer+sql+server+jobs&rnum=2&hl=de#3c3ff63979b36397
AND here:
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_thread/thread/795ec20703b7b535/06aba889e67fe33c?q=jens+S%C3%BC%C3%9Fmeyer+sql+server+jobs&rnum=3&hl=de#06aba889e67fe33c
"bjones" wrote:
> Hi
> Can sql server jobs we viewed by account without sa access? If so what
> permissions need to be granted?|||Hi
granted access to the RepositoryUser role in msdb and still can't see the
jobs. Tried explicitly granting exec to some job related procedures and
select on tables in msdb and still no luck. Don't want to grant sa. There
must be an alternative?
"Jens Sü�meyer" wrote:
> Answered here:
> http://groups.google.de/group/microsoft.public.sqlserver.security/browse_thread/thread/1f7ed5fedba0a853/3c3ff63979b36397?q=jens+S%C3%BC%C3%9Fmeyer+sql+server+jobs&rnum=2&hl=de#3c3ff63979b36397
>
> AND here:
> http://groups.google.de/group/microsoft.public.sqlserver.server/browse_thread/thread/795ec20703b7b535/06aba889e67fe33c?q=jens+S%C3%BC%C3%9Fmeyer+sql+server+jobs&rnum=3&hl=de#06aba889e67fe33c
>
> "bjones" wrote:
> > Hi
> >
> > Can sql server jobs we viewed by account without sa access? If so what
> > permissions need to be granted?