Granting User Privileges??

I have setup an SQL MSDE instance on my PC and I currently finished working
on the BE in an access 2003 project. I believe it is now time to define
roles, users and grant each one its privileges, but how do I do that?
I know about GRANT, REVOKE etc, but
1. Where do I type these?
2. Is all this information encrypted?
3. When installing the MSDE I set a password what is this all about? Will
it have any impact on user level security?
4. Is there a visual tool to handle user level security, such as in an
Access database?
5. As I indend to create a distributed database application is these
security scheme in accordance to that?
6 and final. How can I let the superuser (aka the superior user of the
client) grant privileges to other users, after granting him the option to
grant? eg form?
Some answers inline
"Dimitrios Tanis" <jtanis@.mycosmos.gr> wrote in message news:O%23XXjhBnFHA.3608@.TK2MSFTNGP15.phx.gbl...
>I have setup an SQL MSDE instance on my PC and I currently finished working on the BE in an access 2003 project. I
>believe it is now time to define roles, users and grant each one its privileges, but how do I do that?
> I know about GRANT, REVOKE etc, but
> 1. Where do I type these?
In a script, run with osql.exe against the database is one way.
Query Analyser from a developer pc or pc with sql server installed is another

> 2. Is all this information encrypted?
Not unless it is embedded in your application, and only run locally or through an encrypted connection.
Even if it was scripting the database will get this configuration type of information back. The encyption in
SQL Server 2000 / MSDE is for view, triggers and stored procedures - i.e. code, and is crackable anyway.

> 3. When installing the MSDE I set a password what is this all about? Will it have any impact on user level security?
Depends on what authentication mode you use - with windows authentication, the only password you
have to worry about is the sa one on the install, and this is only a problem if someone at some stage
changes the security to SQL/Mixed.

> 4. Is there a visual tool to handle user level security, such as in an Access database?
If you map windows groups to the SQL database roles, then your user management is done under windows itself.
The beta for SQL 2005 Express includes a GUI tool, and there is a web based one for 2000.
http://www.microsoft.com/downloads/d...displaylang=en
http://www.microsoft.com/downloads/d...DisplayLang=en
Apart from them there is osql.exe for the masochistic, Enterprise Manager for the licensed, or your application
doing sql calls behind the scenes.

> 5. As I indend to create a distributed database application is these security scheme in accordance to that?
Well you are going to have to enable the network libraries, which immediatly gives you an order of magnitude
more security worries. For example a breach in your install of MSDE can grant full access to the PC (xp_cmdshell).
Integrated security is Microsoft's recommendation, but you still need to think about changing network ports and
firewalling.

> 6 and final. How can I let the superuser (aka the superior user of the client) grant privileges to other users, after
> granting him the option to grant? eg form?
see 4)
also suggest reading security related info from
http://msdn.microsoft.com/library/de...stsql_84xl.asp
Regards
AJ

Granting stored procedure execute permissions from ASP.NET?

Bit of an emergency!
I do not have direct access to our SQL Server but I have full FTP access to the web server and have the db Username/passwords.
I need to grant execute permissions on a stored procedure, can I do this from an asp/ASP.NET page?
The DB guys take 24 hours to run a script against the database!
Any help would be greatfully recieved.
Rich
You can use a SqlCommand object, and set the command text to something like this:
Grant Execute Onsp_name To ASPNET
Then use .ExecuteNonQuery()

granting public permissions to another role

We need to revoke all insert/update/delete access from public. So this won't
affect users I wanted to create a new role and grant it all of these excess
permissions from public, then revoke the permissions from public. It looks
like there are thousands of grants that need to be revoked - can anyone thin
k
of a way to script this?
Thank you in advance.One method is to generate the script using Transact-SQL. You can tweak the
example below to generate the desired script. This example script ignores
system objects and doesn't handle column permissions.
SET NOCOUNT ON
SELECT
CASE [p].[protecttype]
WHEN 204 THEN 'GRANT '
WHEN 205 THEN 'GRANT '
WHEN 206 THEN 'DENY '
END +
CASE [p].[action]
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 224 THEN 'EXECUTE'
WHEN 26 THEN 'REFERENCES'
END + ' ON ' +
QUOTENAME(USER_NAME([o].[uid])) + '.' +
QUOTENAME([o].[name]) + ' TO ' +
QUOTENAME([u].[name]) +
CASE WHEN [p].[protecttype] = 204 THEN ' WITH GRANT OPTION' ELSE '' END
FROM
[sysobjects] AS [o]
JOIN
[sysprotects] AS [p] ON
[p].[id] = [o].[id]
JOIN
[sysusers] AS [u] ON
[p].[uid] = [u].[uid]
WHERE
OBJECTPROPERTY([o].[id], 'IsMSShipped') = 0 AND
[u].[name] = 'public'
Hope this helps.
Dan Guzman
SQL Server MVP
"Bobsie" <Bobsie@.discussions.microsoft.com> wrote in message
news:745289F7-F2CF-4079-8A35-66974CFA73F3@.microsoft.com...
> We need to revoke all insert/update/delete access from public. So this
> won't
> affect users I wanted to create a new role and grant it all of these
> excess
> permissions from public, then revoke the permissions from public. It looks
> like there are thousands of grants that need to be revoked - can anyone
> think
> of a way to script this?
> Thank you in advance.|||Thanks for the help - and when it comes to revoking the permissions from
"public" I could use a similar script using "revoke" instead of "grant"?
"Dan Guzman" wrote:

> One method is to generate the script using Transact-SQL. You can tweak th
e
> example below to generate the desired script. This example script ignores
> system objects and doesn't handle column permissions.
> SET NOCOUNT ON
> SELECT
> CASE [p].[protecttype]
> WHEN 204 THEN 'GRANT '
> WHEN 205 THEN 'GRANT '
> WHEN 206 THEN 'DENY '
> END +
> CASE [p].[action]
> WHEN 193 THEN 'SELECT'
> WHEN 195 THEN 'INSERT'
> WHEN 196 THEN 'DELETE'
> WHEN 197 THEN 'UPDATE'
> WHEN 224 THEN 'EXECUTE'
> WHEN 26 THEN 'REFERENCES'
> END + ' ON ' +
> QUOTENAME(USER_NAME([o].[uid])) + '.' +
> QUOTENAME([o].[name]) + ' TO ' +
> QUOTENAME([u].[name]) +
> CASE WHEN [p].[protecttype] = 204 THEN ' WITH GRANT OPTION' ELSE '' END
> FROM
> [sysobjects] AS [o]
> JOIN
> [sysprotects] AS [p] ON
> [p].[id] = [o].[id]
> JOIN
> [sysusers] AS [u] ON
> [p].[uid] = [u].[uid]
> WHERE
> OBJECTPROPERTY([o].[id], 'IsMSShipped') = 0 AND
> [u].[name] = 'public'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bobsie" <Bobsie@.discussions.microsoft.com> wrote in message
> news:745289F7-F2CF-4079-8A35-66974CFA73F3@.microsoft.com...
>
>|||Yes, Bobsie, the script I posted was developed to script existing
permissions. You'll need to modify it so that modified permission scripts
are generated instead.
Run the script once to extract the public permissions with your new role
hard-coded as the grantee instead of public'. The generated script will
looks something like:
GRANT SELECT ON MyTable TO MyNewRole
GRANT EXECUTE ON MyProc TO MyNewRole
Then run the script again with a hard-coded REVOKE instead of the GRANT/DENY
CASE statement so the second script generated will be like:
REVOKE SELECT ON MyTable TO public
REVOKE EXECUTE ON MyProc TO public
Be sure to review the generated scripts before running in your environment.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bobsie" <Bobsie@.discussions.microsoft.com> wrote in message
news:3949EB00-7D35-4EC1-9920-249ABF69BBEC@.microsoft.com...
> Thanks for the help - and when it comes to revoking the permissions from
> "public" I could use a similar script using "revoke" instead of "grant"?
>
> "Dan Guzman" wrote:
>sql

Granting Permissions using SQL 2005 Schema...

All,

I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema.

I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users].

I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema.

Anyone have any ideas?

Thanks,

Justin

They would need alter schema to drop tables in the schema.

GRANT ALTER ON SCHEMA::[Sandbox] TO [Sandbox Users]

-Sue

|||

CONTROL should cover ALTER and DELETE. My guess is that the users from that group have been denied some permission that is affecting their DELET statements.

You can make use of fn_my_permissions and has_perm_by_name to find out the actual permissions on the object, for example:

-- Connected as/impersonating a member of Sandbox users

--

SELECT * FROM fn_my_permissions( 'Sandbox', 'schema' )

go

SELECT has_perms_by_name( 'Sandbox.SampleTable', 'object', 'DELETE' )

go

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for catching that Raul...not even sure what I was thinking last night. Or not thinking at that moment.

-Sue

|||

Raul,

Thanks... that will help. I will be working with the user this morning to see if I can figure out why he is having this problem.

It looks like the permissions are fine. I was helping him out this morning and I think he has an issue with the package he was trying to run, bu the fn_my_permissions helped out tremendously.

Thanks!

Justin

Granting Permissions using SQL 2005 Schema...

All,

I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema.

I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users].

I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema.

Anyone have any ideas?

Thanks,

Justin

They would need alter schema to drop tables in the schema.

GRANT ALTER ON SCHEMA::[Sandbox] TO [Sandbox Users]

-Sue

|||

CONTROL should cover ALTER and DELETE. My guess is that the users from that group have been denied some permission that is affecting their DELET statements.

You can make use of fn_my_permissions and has_perm_by_name to find out the actual permissions on the object, for example:

-- Connected as/impersonating a member of Sandbox users

--

SELECT * FROM fn_my_permissions( 'Sandbox', 'schema' )

go

SELECT has_perms_by_name( 'Sandbox.SampleTable', 'object', 'DELETE' )

go

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for catching that Raul...not even sure what I was thinking last night. Or not thinking at that moment.

-Sue

|||

Raul,

Thanks... that will help. I will be working with the user this morning to see if I can figure out why he is having this problem.

It looks like the permissions are fine. I was helping him out this morning and I think he has an issue with the package he was trying to run, bu the fn_my_permissions helped out tremendously.

Thanks!

Justin

Granting permissions to stored procedures

I am using the following code to grant user access to the stored procedures in my database. However, it does not appear to be working because I am getting an access denied message when running the application as the user.

Here is the code I am using:

Code Snippet

' Grant privileges

Dim ExecutePrivilege As New ObjectPermissionSet

ExecutePrivilege.Execute = True

' Grant privileges to all non-system stored procs

' The following line improves performance

SmoServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")

For Each sp As StoredProcedure In db.StoredProcedures

If Not sp.IsSystemObject Then

sp.Grant(ExecutePrivilege, loginName)

End If

Next

Is there something else I need to do, like a Save or Refresh or something? (I've stepped through the code and it *is* executing for each of my stored procedures. It just does not appear to actually have updated the priviledge.)

Any tips or ideas would be appreciated.

Thanks!

Security related commands are always executed directly, unless you change the setting from the context to only script the commands like the following statement does:

svr.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

The reflected sources show that a property is set for this which is called

so.ForDirectExecution = true;

Did you have a look in the profiler to see if the commands are arriving at the server and are eventually bounced back due to errors occuring during applying the script ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I skipped the profiler and went right to the database. I can view permissions, and they are actually set correctly. So the code is executing. (Should have thought of that before I posted!<G>)

The problem is that when I try to access any of the stored procs (or the tables), I get a "permission was denied on the object" message. So something else is obviously wrong.

This particular code happens to be in VB6, accessing SQLServer Express. When the *same* code accesses a SQL Server 2000 database, it runs without this error.

Any idea what could be wrong here? Or at this point do I need to move the question elsewhere since it does not appear to be an SMO problem.

sql

Granting consultant access to database

Hi,
I need to grant a consultant access to one database on an sql server.
I do not want to give him remote access to the server.
I have installed the client tools (Enterprise manager) on his laptop.
I put him in Users and made him db_admin.
However, when he connects to the server and clicks on the tables it says he
is an invalid user error 916.
Any ideas how i can do this so its most secure. He needs to examine the
tables on the server. The SQL server is 2000.
ThanksRoberto,
Something that you might check. If you ran sp_grantdbaccess to make your
consultant a user in the database, that does not prove that he has access to
the server. You may also need to:
EXEC sp_grantlogin 'Corporate\BobTheConsultant'
If that is OK, then you should also check that the default database of his
login is a database to which he has access. The 'not a user' may come from
the default database.
RLF
"Roberto R" <RobertoR@.discussions.microsoft.com> wrote in message
news:BE654755-B958-48AB-9B5A-BCF215916729@.microsoft.com...
> Hi,
> I need to grant a consultant access to one database on an sql server.
> I do not want to give him remote access to the server.
> I have installed the client tools (Enterprise manager) on his laptop.
> I put him in Users and made him db_admin.
> However, when he connects to the server and clicks on the tables it says
> he
> is an invalid user error 916.
> Any ideas how i can do this so its most secure. He needs to examine the
> tables on the server. The SQL server is 2000.
> Thanks

Granting Access to Modify jobs

How do you grant a user access to modify SQL Server jobs without granting
them full access to the Server. I want them to have access to certain
databases and also have the ability to modiy/create jobs.On Feb 14, 1:47 pm, Don <D...@.discussions.microsoft.com> wrote:
> How do you grant a user access to modify SQL Server jobs without granting
> them full access to the Server. I want them to have access to certain
> databases and also have the ability to modiy/create jobs.
They will have to be a Login on the Server, a User in each database
and have some permission in msdb.
See SQL Server 2005 Books Online topics:
Selecting an Account for the SQL Server Agent Service - http://
msdn2.microsoft.com/en-us/library/ms191543.aspx
Implementing SQL Server Agent Security - http://msdn2.microsoft.com/en-
us/library/ms190926.aspx
Security for SQL Server Agent Administration - http://
msdn2.microsoft.com/en-us/library/ms190978.aspx
SQL Server Agent Fixed Database Roles - http://msdn2.microsoft.com/en-
us/library/ms188283.aspx|||We've been unable to get the Agent Roles to work properly for domain account
s
in our environment. A SQL Login that is added to the SQLAgentOperatorRole is
able to create a job (with that login as the owner), edit the job, and
perform all other tasks as documented for this role. HOWEVER, if we add a
domain account to this role, it can create the job but cannot edit it. The
domain account is shown as the owner of the job. The domain account can star
t
the job but can't see the status and can't tell if it's running or not. It
can delete the job and make a new one but the button that should say 'edit'
says 'view'.
"Steve" wrote:

> On Feb 14, 1:47 pm, Don <D...@.discussions.microsoft.com> wrote:
> They will have to be a Login on the Server, a User in each database
> and have some permission in msdb.
> See SQL Server 2005 Books Online topics:
> Selecting an Account for the SQL Server Agent Service - http://
> msdn2.microsoft.com/en-us/library/ms191543.aspx
> Implementing SQL Server Agent Security - http://msdn2.microsoft.com/en-
> us/library/ms190926.aspx
> Security for SQL Server Agent Administration - http://
> msdn2.microsoft.com/en-us/library/ms190978.aspx
> SQL Server Agent Fixed Database Roles - http://msdn2.microsoft.com/en-
> us/library/ms188283.aspx
>
>
>

Granting access to an Integration Services server?

I have followed the instructions in this topic: http://msdn2.microsoft.com/en-us/library/aa337083.aspx and I have read Kirk Haselden's blog post on the subject (which contains the same info) and I still CANNOT manage to grant non-administrators the ability to access an IS server. They constantly get the Access is Denied error. Has ANYONE actually gotten this to work? If so, could you please share what you did?Jeff,
Try looking at this and see if this helps:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=701950&SiteID=1|||

Yes, Yes, YES! That contained the missing puzzle piece!

I'll reiterate for posterity:

The MSDN documentation at http://msdn2.microsoft.com:80/en-us/library/aa337083.aspx is incomplete. It mentions setting custom permissions in the Launch and Activation Permissions group but it fails to mention that you must also set custom permissions in the Access Permissions group.

|||

JeffJohnsonMVPVB wrote:

Yes, Yes, YES! That contained the missing puzzle piece!

I'll reiterate for posterity:

The MSDN documentation at http://msdn2.microsoft.com:80/en-us/library/aa337083.aspx is incomplete. It mentions setting custom permissions in the Launch and Activation Permissions group but it fails to mention that you must also set custom permissions in the Access Permissions group.

Be sure that you click on the feedback comment button at the top right of that article.

Granting Access permissions

Hi

I am using SQL Express2005 on my local machine. How do I grant access permissions to ASPNET in order to log onto a database? I have the SQL management studio installed to do this but I can't seem to find the option to grant permissions.

At the moment the access is granted to MYMACHINE_NAME\MY_USER_NAME.

I am using VS2003

Thanks

You need to add SQL login for that account: open Management Studio->connect to SQL Express->expand Security->Logins)>new login->new a Windows Authentication login named MACHINENAME\ASPNET->map the login to databases that it will access, and give proper permissions to it.|||

Hi,

Many Thanks...!

Grant Read only access to ALL databases

Let me start off by apologizing for asking such a "noob" question.
I have a SQL2000 server (on my AD domain) with a script that creates a
new database everyday, naming them by the MMDDYY format. For example,
the database for March 25, 2006 would be named "032506". And so on.
I would like to have a group of users that have "READ" only access to
these databases.
I've created a group on Active Director called "DBReader".
I've added the group to the SQL Server Enterprise Manager -> Security
-> Logins
This is were I'm stumped.
When I right click on the group I've added to the SQL Server Enterprise
Manager, I get three tabs, General, Server Roles, and Database Access.
I don't want to use the Server Roles tab because all the roles give too
much access (I only want to give READ access).
The Database Access tab allows me to assign READ access to "existing"
databases, what about new databases that gets created tomorrow and the
next day?
How can I assign "READ" access using group permissions to ALL existing
and NEW databases thats created in the future?
I'm hoping there's a simple solution and don't have to resort to
scheduling a script to add permissions to new databases everyday.
Thx for reading!
"me" <mnkykng@.gmail.com> wrote in message
news:1143504145.539215.309930@.g10g2000cwb.googlegr oups.com...
> Let me start off by apologizing for asking such a "noob" question.
> I have a SQL2000 server (on my AD domain) with a script that creates a
> new database everyday, naming them by the MMDDYY format. For example,
> the database for March 25, 2006 would be named "032506". And so on.
> I would like to have a group of users that have "READ" only access to
> these databases.
> I've created a group on Active Director called "DBReader".
> I've added the group to the SQL Server Enterprise Manager -> Security
> -> Logins
> This is were I'm stumped.
> When I right click on the group I've added to the SQL Server Enterprise
> Manager, I get three tabs, General, Server Roles, and Database Access.
> I don't want to use the Server Roles tab because all the roles give too
> much access (I only want to give READ access).
> The Database Access tab allows me to assign READ access to "existing"
> databases, what about new databases that gets created tomorrow and the
> next day?
> How can I assign "READ" access using group permissions to ALL existing
> and NEW databases thats created in the future?
> I'm hoping there's a simple solution and don't have to resort to
> scheduling a script to add permissions to new databases everyday.
>
New databases are created as copies of the Model database. Make the change
there.
David
|||Thx for replying!
So just to clarify.
If I use the SQL Server Enterprise Manager and add my AD group to
Database -> Model -> Users.
Double click the AD group I just added.
And check the box labeled "db_datareader" under the Database role
membership, any new databases that are created will get read access
permissions assigned automatically to my AD group?
sql

Grant Read only access to ALL databases

Let me start off by apologizing for asking such a "noob" question.
I have a SQL2000 server (on my AD domain) with a script that creates a
new database everyday, naming them by the MMDDYY format. For example,
the database for March 25, 2006 would be named "032506". And so on.
I would like to have a group of users that have "READ" only access to
these databases.
I've created a group on Active Director called "DBReader".
I've added the group to the SQL Server Enterprise Manager -> Security
-> Logins
This is were I'm stumped.
When I right click on the group I've added to the SQL Server Enterprise
Manager, I get three tabs, General, Server Roles, and Database Access.
I don't want to use the Server Roles tab because all the roles give too
much access (I only want to give READ access).
The Database Access tab allows me to assign READ access to "existing"
databases, what about new databases that gets created tomorrow and the
next day?
How can I assign "READ" access using group permissions to ALL existing
and NEW databases thats created in the future?
I'm hoping there's a simple solution and don't have to resort to
scheduling a script to add permissions to new databases everyday.
Thx for reading!"me" <mnkykng@.gmail.com> wrote in message
news:1143504145.539215.309930@.g10g2000cwb.googlegroups.com...
> Let me start off by apologizing for asking such a "noob" question.
> I have a SQL2000 server (on my AD domain) with a script that creates a
> new database everyday, naming them by the MMDDYY format. For example,
> the database for March 25, 2006 would be named "032506". And so on.
> I would like to have a group of users that have "READ" only access to
> these databases.
> I've created a group on Active Director called "DBReader".
> I've added the group to the SQL Server Enterprise Manager -> Security
> -> Logins
> This is were I'm stumped.
> When I right click on the group I've added to the SQL Server Enterprise
> Manager, I get three tabs, General, Server Roles, and Database Access.
> I don't want to use the Server Roles tab because all the roles give too
> much access (I only want to give READ access).
> The Database Access tab allows me to assign READ access to "existing"
> databases, what about new databases that gets created tomorrow and the
> next day?
> How can I assign "READ" access using group permissions to ALL existing
> and NEW databases thats created in the future?
> I'm hoping there's a simple solution and don't have to resort to
> scheduling a script to add permissions to new databases everyday.
>
New databases are created as copies of the Model database. Make the change
there.
David|||Thx for replying!
So just to clarify.
If I use the SQL Server Enterprise Manager and add my AD group to
Database -> Model -> Users.
Double click the AD group I just added.
And check the box labeled "db_datareader" under the Database role
membership, any new databases that are created will get read access
permissions assigned automatically to my AD group?

Grant Read only access to ALL databases

Let me start off by apologizing for asking such a "noob" question.
I have a SQL2000 server (on my AD domain) with a script that creates a
new database everyday, naming them by the MMDDYY format. For example,
the database for March 25, 2006 would be named "032506". And so on.
I would like to have a group of users that have "READ" only access to
these databases.
I've created a group on Active Director called "DBReader".
I've added the group to the SQL Server Enterprise Manager -> Security
-> Logins
This is were I'm stumped.
When I right click on the group I've added to the SQL Server Enterprise
Manager, I get three tabs, General, Server Roles, and Database Access.
I don't want to use the Server Roles tab because all the roles give too
much access (I only want to give READ access).
The Database Access tab allows me to assign READ access to "existing"
databases, what about new databases that gets created tomorrow and the
next day?
How can I assign "READ" access using group permissions to ALL existing
and NEW databases thats created in the future?
I'm hoping there's a simple solution and don't have to resort to
scheduling a script to add permissions to new databases everyday.
Thx for reading!"me" <mnkykng@.gmail.com> wrote in message
news:1143504145.539215.309930@.g10g2000cwb.googlegroups.com...
> Let me start off by apologizing for asking such a "noob" question.
> I have a SQL2000 server (on my AD domain) with a script that creates a
> new database everyday, naming them by the MMDDYY format. For example,
> the database for March 25, 2006 would be named "032506". And so on.
> I would like to have a group of users that have "READ" only access to
> these databases.
> I've created a group on Active Director called "DBReader".
> I've added the group to the SQL Server Enterprise Manager -> Security
> -> Logins
> This is were I'm stumped.
> When I right click on the group I've added to the SQL Server Enterprise
> Manager, I get three tabs, General, Server Roles, and Database Access.
> I don't want to use the Server Roles tab because all the roles give too
> much access (I only want to give READ access).
> The Database Access tab allows me to assign READ access to "existing"
> databases, what about new databases that gets created tomorrow and the
> next day?
> How can I assign "READ" access using group permissions to ALL existing
> and NEW databases thats created in the future?
> I'm hoping there's a simple solution and don't have to resort to
> scheduling a script to add permissions to new databases everyday.
>
New databases are created as copies of the Model database. Make the change
there.
David|||Thx for replying!
So just to clarify.
If I use the SQL Server Enterprise Manager and add my AD group to
Database -> Model -> Users.
Double click the AD group I just added.
And check the box labeled "db_datareader" under the Database role
membership, any new databases that are created will get read access
permissions assigned automatically to my AD group?

Grant Privileges

I am trying to give limited privileges to a login account on a table, but it
doesn't seem to work. I have a link table in Access 97. Some columns are
supposed to be read only and some columns are supposed to be read-only.
I use the script below
revoke all privileges on stores to shipdirect1
grant update on stores(creditlimit) to shipdirect1
What am I doing wrong?I havn't tested this but you cud always try removing the user and login and
adding it back.
"Arne" wrote:

> I am trying to give limited privileges to a login account on a table, but
it
> doesn't seem to work. I have a link table in Access 97. Some columns are
> supposed to be read only and some columns are supposed to be read-only.
> I use the script below
> revoke all privileges on stores to shipdirect1
> grant update on stores(creditlimit) to shipdirect1
> What am I doing wrong?

Grant permissions

Hi all,
Can I grant select only permission on all objects in the database? I have users that I need to give view access only on stored procedures, triggers, and functions. Thanks.db_datareader (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_6ndx.asp).

-PatPsql

grant or deny access to db

I want to avoid users to connect to my dB via odbc
if the user do not use my front application.
If the user try to connect to the db via Acces, Excel or
any other, I want the db to refuse the acces.
How can I do so?
Using application roles would be one option. You can find more info on
application roles in books online.
-Sue
On Fri, 1 Oct 2004 09:32:46 -0700, "fredy"
<anonymous@.discussions.microsoft.com> wrote:

>I want to avoid users to connect to my dB via odbc
>if the user do not use my front application.
>If the user try to connect to the db via Acces, Excel or
>any other, I want the db to refuse the acces.
>How can I do so?

Grant Database Access

I am setting up report services after I installed the report server add-ins
on sharepoint 2007.

at first step, Reporting services integration, I put
report server web service URL -- http://ssrsdev1/reportserver
authentication mode -- windows authentication
it went OK.

at second step, Grant Database Access,
I put ssrsdev1 for server name (default instance) and then click OK and then
I put the user name (a member of local admin group on ssrsdev1) and password,
but I always got errors says:
Unable to log on with the given username and password.
what wrong here? what should I check? I am sure the user and password is
right and I can log on to ssrsdev1 physically with admin right without any
problem.
thanks for any thought on this.
zwp

Is SharePoint 2007 also installed on ssrsdev1?

If not, is this member of local admin group on ssrsdev1 a domain user or a local user (on ssrsdev1)?

thanx,
Prash

|||

we installed sharepoint 2007 bianry on ssrsdev1 (report server with sp2). and the user is a domain user and a member of local admin group. Actually, the report server, sql server and sharepoint all run under domain user (also a member of local admin group). When we tried to use user name domain\user and we got a error says: the group name can't find; when we use user or user@.domain.com to connect, we got the error I posted before. We tried to reinstall the sharepoint 2007 and still got same problem.

Thanks in advance for your help.

D.

|||

In you care you should use <domain_name>\<user> format.

In any case, in "Grant Database Access" we essentially try to add the Reporting Services service accounts to the WSS_WPG windows group. The WSS_WPG windows group should have been created when you installed SharePoint.

Can you verfy whether ssrsdev1 has a WSS_WPG windows group?

thanx,
Prash

|||

the windows group are on both report servers (ssrsdev1 and it also is the content database server for sharepoint server) and sharepoint server (spserver). I have manually added the user account into this grouop in both server, but I still get the same error.

Actually, I installed and running everything (report server and its services, sql server, and sharepoint, etc.) with the same domain user which is a member of local admin group on both boxes, therefore, the user should have access to everything, but I don’t understand why I am still have the problem with ‘grant database access’. Is it a bug?

BTW, we use sharepoing 2007 and sp2 December 2006 CTP.

Thanks,

zwp

|||

I just posted a message. Sorry it was my bad. I might mistype something. It works now.

However, when I go to next step:

Set server defaults

I got error message:

Server was unable to process request. > The request failed with HTTP status 401: Unauthorized.

I wonder it comes from sharepoint IIS or report server IIS? the user (again) I used are the local admin on both box, why still "Unauthorized "?

BTW, in sp2 BOL, it says: Verify the installation

use this address:

http://example-server-name:8081/reportserver

the example-server-name is report server name or sharepoint server name? I have tried both but none of them work. Maybe it is the result of HTTP status 401 above or something else

thanks again.

zwp.

|||

http://example-server-name:<port>/reportserver

example-server-name = the server on which Report Server is installed

<port> = the actual TCP port that your web site containing the ReportServer virtual directory is setup for. Note: 8081 is just an example.

Hope this helps.

thanx,
Prash

|||

could someone let me know how to find user :'sharepoint\system' since I can't find it in computer manager. I bet it is a system account used by sharepoint but how to set the permission to it. I got following

The permissions granted to user 'SHAREPOINT\system' are insufficient for performing this operation. > The permissions granted to user 'SHAREPOINT\system' are insufficient for performing this operation.

when I at screen:

Reporting Services Server Defaults

thanks.

|||

Generally you should open another thread for a different issue.

In any case, are you able to upload / view reports in other SharePoint web sites?

Additionally, are you using a built-in machine account, such as Network Service as the identity of the application pool that hosts the SharePoint Central Admin web site?

thanx,
Prash

|||

Hi,

I've experienced the same problem.

In "Grant Database Access" page, when I entered a domain user account e.g. domain-name\paul (with SQL server local admin right), it pop-up:

The group name could not be found

In my SharePoint Server, there is a WSS_WPG windows group which has my domain account e.g. domain-name\paul

My SQL server 2005 with SP 2 is installed in another server.

Any advice?

Paul

|||

The WSS_WPG Windows group needs to also be present on the Report Server machine you specify as the "Server Name" in the "Grant Database Access" web page.

HTH
Prash

|||

Thanks.

Actually, I can setup the SharePoint Integration in a single-box (MOSS+SQL). So, as you said, WSS_WPG is in the same machine. However, as my SQL server is installed in another server. What can I do? (I did create a group called WSS_WPG manually in the SQL server, but still failed)

Any idea?

Paul

|||

Correct if I am wrong, but it looks like Reporting Services is installed on your SQL Server machine. If yes, then you still need to install the SharePoint Object Model on the SQL Server machine.

For a multi-machine setup refer to "How to: Install the Windows SharePoint Services Object Model on a Report Server Computer" in SQL BOL.

HTH
Prash

Grant Database Access

I am setting up report services after I installed the report server add-ins
on sharepoint 2007.

at first step, Reporting services integration, I put
report server web service URL -- http://ssrsdev1/reportserver
authentication mode -- windows authentication
it went OK.

at second step, Grant Database Access,
I put ssrsdev1 for server name (default instance) and then click OK and then
I put the user name (a member of local admin group on ssrsdev1) and password,
but I always got errors says:
Unable to log on with the given username and password.
what wrong here? what should I check? I am sure the user and password is
right and I can log on to ssrsdev1 physically with admin right without any
problem.
thanks for any thought on this.
zwp

Is SharePoint 2007 also installed on ssrsdev1?

If not, is this member of local admin group on ssrsdev1 a domain user or a local user (on ssrsdev1)?

thanx,
Prash

|||

we installed sharepoint 2007 bianry on ssrsdev1 (report server with sp2). and the user is a domain user and a member of local admin group. Actually, the report server, sql server and sharepoint all run under domain user (also a member of local admin group). When we tried to use user name domain\user and we got a error says: the group name can't find; when we use user or user@.domain.com to connect, we got the error I posted before. We tried to reinstall the sharepoint 2007 and still got same problem.

Thanks in advance for your help.

D.

|||

In you care you should use <domain_name>\<user> format.

In any case, in "Grant Database Access" we essentially try to add the Reporting Services service accounts to the WSS_WPG windows group. The WSS_WPG windows group should have been created when you installed SharePoint.

Can you verfy whether ssrsdev1 has a WSS_WPG windows group?

thanx,
Prash

|||

the windows group are on both report servers (ssrsdev1 and it also is the content database server for sharepoint server) and sharepoint server (spserver). I have manually added the user account into this grouop in both server, but I still get the same error.

Actually, I installed and running everything (report server and its services, sql server, and sharepoint, etc.) with the same domain user which is a member of local admin group on both boxes, therefore, the user should have access to everything, but I don’t understand why I am still have the problem with ‘grant database access’. Is it a bug?

BTW, we use sharepoing 2007 and sp2 December 2006 CTP.

Thanks,

zwp

|||

I just posted a message. Sorry it was my bad. I might mistype something. It works now.

However, when I go to next step:

Set server defaults

I got error message:

Server was unable to process request. > The request failed with HTTP status 401: Unauthorized.

I wonder it comes from sharepoint IIS or report server IIS? the user (again) I used are the local admin on both box, why still "Unauthorized "?

BTW, in sp2 BOL, it says: Verify the installation

use this address:

http://example-server-name:8081/reportserver

the example-server-name is report server name or sharepoint server name? I have tried both but none of them work. Maybe it is the result of HTTP status 401 above or something else

thanks again.

zwp.

|||

http://example-server-name:<port>/reportserver

example-server-name = the server on which Report Server is installed

<port> = the actual TCP port that your web site containing the ReportServer virtual directory is setup for. Note: 8081 is just an example.

Hope this helps.

thanx,
Prash

|||

could someone let me know how to find user :'sharepoint\system' since I can't find it in computer manager. I bet it is a system account used by sharepoint but how to set the permission to it. I got following

The permissions granted to user 'SHAREPOINT\system' are insufficient for performing this operation. > The permissions granted to user 'SHAREPOINT\system' are insufficient for performing this operation.

when I at screen:

Reporting Services Server Defaults

thanks.

|||

Generally you should open another thread for a different issue.

In any case, are you able to upload / view reports in other SharePoint web sites?

Additionally, are you using a built-in machine account, such as Network Service as the identity of the application pool that hosts the SharePoint Central Admin web site?

thanx,
Prash

|||

Hi,

I've experienced the same problem.

In "Grant Database Access" page, when I entered a domain user account e.g. domain-name\paul (with SQL server local admin right), it pop-up:

The group name could not be found

In my SharePoint Server, there is a WSS_WPG windows group which has my domain account e.g. domain-name\paul

My SQL server 2005 with SP 2 is installed in another server.

Any advice?

Paul

|||

The WSS_WPG Windows group needs to also be present on the Report Server machine you specify as the "Server Name" in the "Grant Database Access" web page.

HTH
Prash

|||

Thanks.

Actually, I can setup the SharePoint Integration in a single-box (MOSS+SQL). So, as you said, WSS_WPG is in the same machine. However, as my SQL server is installed in another server. What can I do? (I did create a group called WSS_WPG manually in the SQL server, but still failed)

Any idea?

Paul

|||

Correct if I am wrong, but it looks like Reporting Services is installed on your SQL Server machine. If yes, then you still need to install the SharePoint Object Model on the SQL Server machine.

For a multi-machine setup refer to "How to: Install the Windows SharePoint Services Object Model on a Report Server Computer" in SQL BOL.

HTH
Prash

Grant ASPNET all access

hello all,

I would like to grant the MACHINENAME\ASPNET user all acccess to my MSSQL database (i.e tables and stored procedures. I can do this through enterprise manager but id rather do it programatically so i can add it to my database creation script. What is the syntax for this?

I thought it would be along the lines of:

Grant all on databasename to localhost\ASPNET.

But obviously its not! Please help!

thanks

TomIn response to my own question, if anyone needs to know:

exec sp_grantlogin N'machine-name\ASPNET'
go
exec sp_grantlogin N'NT AUTHORITY\NETWORK SERVICE'
go
use dbname
go
exec sp_grantdbaccess
@.loginame = 'machine-name\ASPNET'
go
exec sp_grantdbaccess
@.loginame ='NT AUTHORITY\NETWORK SERVICE'
go

(Rename machinename and dbname to your specifics)

The network service user is required just for windows 2003 i believe, i think on xp and 2000 only ASPNET is needed access.

Tom

Grant and Revoke on a column

I am trying to give limited privileges to a login account on a table, but it
doesn't seem to work. I have a link table in Access 97 and my SQL server is
version 7. Some columns are supposed to be read only and some columns are
supposed to be read-only.
I use the script below
revoke all privileges on stores to shipdirect1
grant update on stores(creditlimit) to shipdirect1
What am I doing wrong?Arne,
SQL Server does not support column level permissions. Even if it did it is
doubtful Access supports it. You will be better off setting up a view with
the permitted columns and granting permissions to it to the user.
Ilya
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:CC4D4275-A5EC-4C30-B875-8A5CB2626C65@.microsoft.com...
> I am trying to give limited privileges to a login account on a table, but
it
> doesn't seem to work. I have a link table in Access 97 and my SQL server
is
> version 7. Some columns are supposed to be read only and some columns are
> supposed to be read-only.
> I use the script below
> revoke all privileges on stores to shipdirect1
> grant update on stores(creditlimit) to shipdirect1
> What am I doing wrong?
>|||SQL Server 7.0 that is.
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:OdYOh6GFFHA.2756@.TK2MSFTNGP15.phx.gbl...
> Arne,
> SQL Server does not support column level permissions. Even if it did it is
> doubtful Access supports it. You will be better off setting up a view with
> the permitted columns and granting permissions to it to the user.
> Ilya
> "Arne" <Arne@.discussions.microsoft.com> wrote in message
> news:CC4D4275-A5EC-4C30-B875-8A5CB2626C65@.microsoft.com...
but
> it
> is
are
>|||Actually, SQL Server DOES support column-level permissions and has done so
since the earliest version.
The problem is probably related to linking to the Access table, if access
doesn't support this.
How do you know it doesn't work? Are you getting an error? What does it say?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:OdYOh6GFFHA.2756@.TK2MSFTNGP15.phx.gbl...
> Arne,
> SQL Server does not support column level permissions. Even if it did it is
> doubtful Access supports it. You will be better off setting up a view with
> the permitted columns and granting permissions to it to the user.
> Ilya
> "Arne" <Arne@.discussions.microsoft.com> wrote in message
> news:CC4D4275-A5EC-4C30-B875-8A5CB2626C65@.microsoft.com...
> it
> is
>|||Kalen,
When I update a column that is supposed to be read-only I don't the
exception that I expected.
However I found some MS Access properties that solved the problem for me.
Arne.
"Kalen Delaney" wrote:

> Actually, SQL Server DOES support column-level permissions and has done so
> since the earliest version.
> The problem is probably related to linking to the Access table, if access
> doesn't support this.
> How do you know it doesn't work? Are you getting an error? What does it sa
y?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
> news:OdYOh6GFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>
>