granting select permission

Hi,

How to grant select permission on table of another database from current database.

Ex: I am in database asddb

I want to grant select permission on table "test" which is in database bsddb.

Can anyone please help me in resolving this problem.Please take a look at the GRANT statement in Books Online. You need to switch to the database using USE and run the GRANT statement.

granting select permission

Hi,

How to grant select permission on table of another database from current database.

Ex: I am in database asddb

I want to grant select permission on table "test" which is in database bsddb.

Can anyone please help me in resolving this problem.Please take a look at the GRANT statement in Books Online. You need to switch to the database using USE and run the GRANT statement.

Granting SA rights to Sr. Developers in Small ITS Groups when ther

I just finished reading several articles on the SQL Server Security Checklis
t
or Best Practices - and I could use some advise. Our ITS group is too small
to have a full time [application] DBA for SQL Servers so only Networking has
SA rights on the servers which in the past was not a problem. But now that
we're starting to develop/implement new Coldfusion and SQL Server Database
applications as we head toward switching from static HTML to a dynamic
database driven multiple server production environment that will use both a
staging and a development server. Since these are new technologies to this
environment and the production servers that only contain public information
(i.e. do not contain high risk data), Development has requested read-only
access to the production and staging servers to look at the data integrity,
check the rev. number on the code, and look at IIS, CF, and SQL logs and
configuration files, as needed. Development has also requested SA rights fo
r
their Sr. Developers only on the development server (i.e not the product
servers) to be used for testing replication, running diagnostics, reviewing
logs, create test applications users/roles to test permissions, test
configuration settings and permission issues (we are also implementing activ
e
directory) but access was denied to both requests. With one month left
before going live with the new production servers on IIS/CF/SQL, development
is concerned with the time delays with testing or replicating problems on th
e
development server and the inability to diagnose production problems with
outdated logs (vs real time monitors of IIS/CF).
The networking staff are competent at network issue but are not DBAs and doe
not have knowledge of how setting and permissions in SQL Server (not to
mention IIS or CF) will affect applications. How should SQL Server
roles/permission be split between Networking and Development on the
Development server in our small environment (note: our Sr Developers have 5
-
25 years of development experience including commercial DB engineering)?hi
there are no hard and fast rules to set a role to the users of the database.
it depends on the policy that u draft.
U need not have a full time DBA, eventhough its advised to have. Senior
developers with good admin skills can take up the role.
the person should have knowledge on taking backups and crash recovery
hope this answers the question
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"JA" wrote:

> I just finished reading several articles on the SQL Server Security Checkl
ist
> or Best Practices - and I could use some advise. Our ITS group is too sma
ll
> to have a full time [application] DBA for SQL Servers so only Networking has
> SA rights on the servers which in the past was not a problem. But now tha
t
> we're starting to develop/implement new Coldfusion and SQL Server Database
> applications as we head toward switching from static HTML to a dynamic
> database driven multiple server production environment that will use both
a
> staging and a development server. Since these are new technologies to th
is
> environment and the production servers that only contain public informatio
n
> (i.e. do not contain high risk data), Development has requested read-only
> access to the production and staging servers to look at the data integrity
,
> check the rev. number on the code, and look at IIS, CF, and SQL logs and
> configuration files, as needed. Development has also requested SA rights
for
> their Sr. Developers only on the development server (i.e not the product
> servers) to be used for testing replication, running diagnostics, reviewin
g
> logs, create test applications users/roles to test permissions, test
> configuration settings and permission issues (we are also implementing act
ive
> directory) but access was denied to both requests. With one month left
> before going live with the new production servers on IIS/CF/SQL, developme
nt
> is concerned with the time delays with testing or replicating problems on
the
> development server and the inability to diagnose production problems with
> outdated logs (vs real time monitors of IIS/CF).
> The networking staff are competent at network issue but are not DBAs and d
oe
> not have knowledge of how setting and permissions in SQL Server (not to
> mention IIS or CF) will affect applications. How should SQL Server
> roles/permission be split between Networking and Development on the
> Development server in our small environment (note: our Sr Developers have
5 -
> 25 years of development experience including commercial DB engineering)?|||It sounds like you're between a political rock and a hard place. The
important thing is to save in your CYA file a copy of the memo informing
management that what's going live cannot be adequately tested without
access, and since access has been denied, that they'll have to live with the
possibility of a meltdown. Once a meltdown occurs, you can be absolutely
sure that you will be given all the access you need (and probably more) in
order to fix the problem. (You might want to plan on an all-night debugging
fest, so be sure to stock up on Mountain Dew.)
Another solution would be to request equipment identical to the production
environment that is dedicated and accessible to the ITS group just for
testing. Of course someone will have to pay for the new equipment, and
you'll probably have to delay going live until the new equipment is
configured and testing is completed.
"JA" <JA@.discussions.microsoft.com> wrote in message
news:9E08BED2-6BEA-46B7-966E-64A6ADE1AD19@.microsoft.com...
> I just finished reading several articles on the SQL Server Security
Checklist
> or Best Practices - and I could use some advise. Our ITS group is too
small
> to have a full time [application] DBA for SQL Servers so only Networking
has
> SA rights on the servers which in the past was not a problem. But now
that
> we're starting to develop/implement new Coldfusion and SQL Server Database
> applications as we head toward switching from static HTML to a dynamic
> database driven multiple server production environment that will use both
a
> staging and a development server. Since these are new technologies to
this
> environment and the production servers that only contain public
information
> (i.e. do not contain high risk data), Development has requested read-only
> access to the production and staging servers to look at the data
integrity,
> check the rev. number on the code, and look at IIS, CF, and SQL logs and
> configuration files, as needed. Development has also requested SA rights
for
> their Sr. Developers only on the development server (i.e not the product
> servers) to be used for testing replication, running diagnostics,
reviewing
> logs, create test applications users/roles to test permissions, test
> configuration settings and permission issues (we are also implementing
active
> directory) but access was denied to both requests. With one month left
> before going live with the new production servers on IIS/CF/SQL,
development
> is concerned with the time delays with testing or replicating problems on
the
> development server and the inability to diagnose production problems with
> outdated logs (vs real time monitors of IIS/CF).
> The networking staff are competent at network issue but are not DBAs and
doe
> not have knowledge of how setting and permissions in SQL Server (not to
> mention IIS or CF) will affect applications. How should SQL Server
> roles/permission be split between Networking and Development on the
> Development server in our small environment (note: our Sr Developers have
5 -
> 25 years of development experience including commercial DB engineering)?|||SA rights on a development box does not sound unreasonable; in fact, I
recommend it, for many of the reasons you list above. Developers need
their own space to break stuff, so that they understand how NOT to
break it in production.
I work in a small shop as well, and currently I serve as both
production and development DBA; we're hiring a production DBA, but
until then I have to document every little thing I do to make sure that
there is a record for accountability depending on the hat I'm wearing.
I think it's OK for developers to have read-only access to
llimited-risk data in production, and they should have full control
over their development environment. And you can tell your bosses I
said so :)
Someone on the Interwebs believes in your cause.
Stu|||On Fri, 29 Jul 2005 22:56:02 -0700, JA wrote:
(snip)
>The networking staff are competent at network issue but are not DBAs and do
e
>not have knowledge of how setting and permissions in SQL Server (not to
>mention IIS or CF) will affect applications. How should SQL Server
>roles/permission be split between Networking and Development on the
>Development server in our small environment (note: our Sr Developers have 5
-
>25 years of development experience including commercial DB engineering)?
Hi JA,
I tend to agree with Stu: SA rights on the dev. server for experienced
DB developers and read rights to non-sensitive stuff on the prod server
sounds reasonable.
On the other hand, the networking staff has been assigned the
responsibility to keep the databases up and running and to protect data
from from unauthorized spreading (I'm sure that this is a terrible
sentence, but I don't know how to put it in correct English - I hope you
do understand what I'm trying to say).
And since it's THEIR responsibility, they are free to take whatever
measures THEY think are needed to accomplish that goals.
Many years ago, I had to do some major reshuffling on a SQL Server
implementation. The sa access I had was enough for about half the things
I needed to do (like shutting down the service, or creating a new file),
but many other tasks (like restarting the service, and deleting files no
longer needed) were only available for the network admins. Of course, I
requested access as network admin, to streamline the process. And of
course, I didn't get it. So I phoned the network admins each time I
needed them to do something:
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - could you delete file XXX on server YYY please?"
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - file AAA has to be moved from drive BBB to CCC."
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - I requested a restart of the SQL Server service some 5
minutes ago, but doesn't seem to be running"
And so on.
Guess how long it took before I DID get some extra rights?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||When I worked help desk, we used to hate guys like you :)
Stu|||On 30 Jul 2005 16:38:27 -0700, Stu wrote:

>When I worked help desk, we used to hate guys like you :)
Hey! I was only trying to get my job done, you know!
(mumble, whine, mumble)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Granting right to all the user sp's

Hello,
I would like to ease granting the right to execute all user sp's by normal u
sers.
By default, AFIK you have to click the EXECUTE option of each and every sp.
I head about a procedure, which could automate this.
Anyone knows this kind of procedure or how to to this?
Thank You
JoachimHi,
use pubs
go
select 'grant execute on ' +name +' to user_name' from sysobjects where
type='p'
Replace the database name and user_name based on your requirement.
After excuting the script you will get a bunch of grant statement in your
result pane, just cut and paste the entire contents and execute it again.
Thanks
Hari
MCDBA
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:404C5069.C80FF06E@.freenet.de...
> Hello,
> I would like to ease granting the right to execute all user sp's by normal
users.
> By default, AFIK you have to click the EXECUTE option of each and every
sp.
> I head about a procedure, which could automate this.
> Anyone knows this kind of procedure or how to to this?
> Thank You
> Joachim|||You can use a procedure like this
use master
go
create procedure sp_grantexec(@.user sysname,@.debug int = 0)
as
set nocount on
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME(@.user)
-- check user exists
if not exists(select * from sysusers where name = @.user)
begin
raiserror('User %s is not a valid user in this database',16,1,@.user)
return -1
end
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') =
0'
if @.debug = 1 print @.sql
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
If @.ret <> 0
begin
raiserror('Error executing command %s',16,1,@.sql)
return -2
end
go
Then you can run it like below to grant user foo exec permissions on all
user stored procedures in the pubs database
use pubs
go
exec sp_grantexec 'foo'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:404C5069.C80FF06E@.freenet.de...
> Hello,
> I would like to ease granting the right to execute all user sp's by normal
users.
> By default, AFIK you have to click the EXECUTE option of each and every
sp.
> I head about a procedure, which could automate this.
> Anyone knows this kind of procedure or how to to this?
> Thank You
> Joachim

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 privilages similar to existing user

I need to grant privilages to a user that are like the privilages of an
existing user. How can I do this?
chuck t.The easiest way would be to write a little SQL-DMO script. Create a user
object and then execute the ListObjectPermissions method. Alternatively,
you can use the Script method and replace the old user with the new user.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:335EB4BA-1BCF-4151-9446-5D4BCA569657@.microsoft.com...
>I need to grant privilages to a user that are like the privilages of an
> existing user. How can I do this?
> --
> chuck t.|||Hi,
please check this if it help you to get list :
http://www.sql-server-performance.c...?TOPIC_ID=10504
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Chuck" wrote:

> I need to grant privilages to a user that are like the privilages of an
> existing user. How can I do this?
> --
> chuck t.

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