Granting Permissions to Roles in SQL Server 2005

I have several years experience working with SQL 2000 and we are now
also working with SQL Server 2005. I have found Management Studio a
bit tricky to work with, MS seems to have gone backwards in a few
areas.
In Enterprise Manager, permission granting was not the best but it was
simple and worked well. In Management Studio granting permissions to
a new role now seems to be very slow and tedious.
First of all it is about six mouse clicks to browse views only, then
select views one at a time (no ctrl or shift + click allowed), click
OK then you need to click through each view and then click Select (or
whatever rights). Clicking through each view for the first time takes
a few seconds on my laptop (1 user, core 2 duo, 2GB ram!).
I personally try and use the mouse as little as possible, keyboard
shortcuts are much faster. The other problem is that frequently the
role does not save correctly, ie. grant rights then click OK, load the
role again and my changes are gone!
We can't use Schemas because our app references dbo.ObjectName
everywhere and it would be impractical to change this to
Schema.ObjectName, or just ObjectName in all of our code. We did this
because referencing objects as dbo.ObjectName was optimised faster
than just ObjectName.
Has anyone else experienced the same problems?Others have mentioned some problems with using the GUI for
managing and viewing security. There is a lot more
complexity to SQL Server 2005 security than there was in
2000. Some of the "views" people were used to in 2000 just
aren't worth as much or as practical in 2005.
But...writing a T-SQL statement worked in 2000, works just
the same in 2005 - you just have more options as to what you
can grant and in what scope in 2005. If you use T-SQL and
scripts for your changes, you have a record which documents
to some degree what you executed, can keep the changes in
source control. In the same regard, you can write a lot
better queries to obtain security information in 2005
compared to 2000.
-Sue
On 12 Feb 2007 16:23:05 -0800, stevo1980@.gmail.com wrote:

>I have several years experience working with SQL 2000 and we are now
>also working with SQL Server 2005. I have found Management Studio a
>bit tricky to work with, MS seems to have gone backwards in a few
>areas.
>In Enterprise Manager, permission granting was not the best but it was
>simple and worked well. In Management Studio granting permissions to
>a new role now seems to be very slow and tedious.
>First of all it is about six mouse clicks to browse views only, then
>select views one at a time (no ctrl or shift + click allowed), click
>OK then you need to click through each view and then click Select (or
>whatever rights). Clicking through each view for the first time takes
>a few seconds on my laptop (1 user, core 2 duo, 2GB ram!).
>I personally try and use the mouse as little as possible, keyboard
>shortcuts are much faster. The other problem is that frequently the
>role does not save correctly, ie. grant rights then click OK, load the
>role again and my changes are gone!
>We can't use Schemas because our app references dbo.ObjectName
>everywhere and it would be impractical to change this to
>Schema.ObjectName, or just ObjectName in all of our code. We did this
>because referencing objects as dbo.ObjectName was optimised faster
>than just ObjectName.
>Has anyone else experienced the same problems?|||(stevo1980@.gmail.com) writes:
> First of all it is about six mouse clicks to browse views only, then
> select views one at a time (no ctrl or shift + click allowed), click
> OK then you need to click through each view and then click Select (or
> whatever rights). Clicking through each view for the first time takes
> a few seconds on my laptop (1 user, core 2 duo, 2GB ram!).
> I personally try and use the mouse as little as possible, keyboard
> shortcuts are much faster. The other problem is that frequently the
> role does not save correctly, ie. grant rights then click OK, load the
> role again and my changes are gone!
The purpose of the more complex dialogue is that will encourage you
to type your statements! As Sue said, this pays off in the long run.
Joking aside, note that there is a Script button in every window, so
you can use the GUI for the first guy to get a template, if you
are not up to pace with the syntax.

> We can't use Schemas because our app references dbo.ObjectName
> everywhere and it would be impractical to change this to
> Schema.ObjectName, or just ObjectName in all of our code. We did this
> because referencing objects as dbo.ObjectName was optimised faster
> than just ObjectName.
No one forces you to use schemas. With "dbo." or not, adding
schemas to an existing application, is a major undertaking. For
the system I work with, schemas would fit in perfectly with what
we call subsystems, but given the size of our app, it's not going
to happen any time soon.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 14, 9:56 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> (stevo1...@.gmail.com) writes:
>
> The purpose of the more complex dialogue is that will encourage you
> to type your statements! As Sue said, this pays off in the long run.
> Joking aside, note that there is a Script button in every window, so
> you can use the GUI for the first guy to get a template, if you
> are not up to pace with the syntax.
>
> No one forces you to use schemas. With "dbo." or not, adding
> schemas to an existing application, is a major undertaking. For
> the system I work with, schemas would fit in perfectly with what
> we call subsystems, but given the size of our app, it's not going
> to happen any time soon.
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodte
chnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousv
ersions/books.mspx
Scripting admin tasks seems the way to go, having an audit trail can
come in handy. There is definitely some nice security functionalty
available with SQL 2005, just the GUI seems to be a bit flawed.
Thanks for your responses.

Grant create proc to

Hello all,
Is there a place to view in SQL Management studio that I have geranted
create proc rights to a certain user?
TIA
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
You can run a query like the following:
USE <database> -- The database the user has permissions in.
--Set the session context to the user.
EXECUTE AS User = '<user_name>';
GO
-- Get the user's permissions on the current database
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-- Set the session context back to you.
REVERT;
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2A8498AB-96BE-49EF-B894-E9EDB3D8F5CB@.microsoft.com...
> Hello all,
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
>
> TIA

Grant create proc to

Hello all,
Is there a place to view in SQL Management studio that I have geranted
create proc rights to a certain user?
TIA> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
You can run a query like the following:
USE <database> -- The database the user has permissions in.
--Set the session context to the user.
EXECUTE AS User = '<user_name>';
GO
-- Get the user's permissions on the current database
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-- Set the session context back to you.
REVERT;
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2A8498AB-96BE-49EF-B894-E9EDB3D8F5CB@.microsoft.com...
> Hello all,
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
>
> TIA

Grant create proc to

Hello all,
Is there a place to view in SQL Management studio that I have geranted
create proc rights to a certain user?
TIA> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
You can run a query like the following:
USE <database> -- The database the user has permissions in.
--Set the session context to the user.
EXECUTE AS User = '<user_name>';
GO
-- Get the user's permissions on the current database
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
-- Set the session context back to you.
REVERT;
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2A8498AB-96BE-49EF-B894-E9EDB3D8F5CB@.microsoft.com...
> Hello all,
> Is there a place to view in SQL Management studio that I have geranted
> create proc rights to a certain user?
>
> TIAsql

Grant Control Causes SQL Server Agent job to fail?

I have a SQL Agent job that applies permissions to a SQL Server 2005
database after it has been restored, it executes in management studio,
it even parses when you click parse with the job step. Yet it fails
every time unless I remove this:
GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
TO "aDomain\aUser"
Errr... Bug?dba
It has been restored from SQL Server 2000? If it has , check out compatibily
level of the database (should be 90)
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1178669352.091335.225000@.n59g2000hsh.googlegroups.com...
>I have a SQL Agent job that applies permissions to a SQL Server 2005
> database after it has been restored, it executes in management studio,
> it even parses when you click parse with the job step. Yet it fails
> every time unless I remove this:
> GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
> TO "aDomain\aUser"
> Errr... Bug?
>|||"Fails" doesn't give us much to go on. Specify an output file for that job step and post the error
messages here.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1178669352.091335.225000@.n59g2000hsh.googlegroups.com...
>I have a SQL Agent job that applies permissions to a SQL Server 2005
> database after it has been restored, it executes in management studio,
> it even parses when you click parse with the job step. Yet it fails
> every time unless I remove this:
> GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
> TO "aDomain\aUser"
> Errr... Bug?
>

Grant Control Causes SQL Server Agent job to fail?

I have a SQL Agent job that applies permissions to a SQL Server 2005
database after it has been restored, it executes in management studio,
it even parses when you click parse with the job step. Yet it fails
every time unless I remove this:
GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
TO "aDomain\aUser"
Errr... Bug?
dba
It has been restored from SQL Server 2000? If it has , check out compatibily
level of the database (should be 90)
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1178669352.091335.225000@.n59g2000hsh.googlegr oups.com...
>I have a SQL Agent job that applies permissions to a SQL Server 2005
> database after it has been restored, it executes in management studio,
> it even parses when you click parse with the job step. Yet it fails
> every time unless I remove this:
> GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
> TO "aDomain\aUser"
> Errr... Bug?
>
|||"Fails" doesn't give us much to go on. Specify an output file for that job step and post the error
messages here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1178669352.091335.225000@.n59g2000hsh.googlegr oups.com...
>I have a SQL Agent job that applies permissions to a SQL Server 2005
> database after it has been restored, it executes in management studio,
> it even parses when you click parse with the job step. Yet it fails
> every time unless I remove this:
> GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
> TO "aDomain\aUser"
> Errr... Bug?
>

Grant Control Causes SQL Server Agent job to fail?

I have a SQL Agent job that applies permissions to a SQL Server 2005
database after it has been restored, it executes in management studio,
it even parses when you click parse with the job step. Yet it fails
every time unless I remove this:
GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
TO "aDomain\aUser"
Errr... Bug?dba
It has been restored from SQL Server 2000? If it has , check out compatibily
level of the database (should be 90)
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1178669352.091335.225000@.n59g2000hsh.googlegroups.com...
>I have a SQL Agent job that applies permissions to a SQL Server 2005
> database after it has been restored, it executes in management studio,
> it even parses when you click parse with the job step. Yet it fails
> every time unless I remove this:
> GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
> TO "aDomain\aUser"
> Errr... Bug?
>|||"Fails" doesn't give us much to go on. Specify an output file for that job s
tep and post the error
messages here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1178669352.091335.225000@.n59g2000hsh.googlegroups.com...
>I have a SQL Agent job that applies permissions to a SQL Server 2005
> database after it has been restored, it executes in management studio,
> it even parses when you click parse with the job step. Yet it fails
> every time unless I remove this:
> GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
> TO "aDomain\aUser"
> Errr... Bug?
>sql

Good sql server programming tool

Hi,
Are there any commercial tools available that allow easy management of sql
server objects? Intellisense, object grouping, hiding system objects etc.?
Thanks
Nick
In article <cfdaep$jpb$1@.phys-news-1.nl.colt.net>,=20
Nick.DELETETHISStansbury@.Sage-Partners.com says...
> Hi,
> Are there any commercial tools available that allow easy management of =
sql
> server objects? Intellisense, object grouping, hiding system objects etc.=
?
>=20
> Thanks
>=20
> Nick
>=20
>=20
>=20
Nick,
We have an IDE designed specifically for the development of SQL code=20
objects that is currently in closed beta. Features include source=20
control, logical grouping, intellisense and much more. =20
We are still accepting select sites to participate in our beta testing,=20
and offer free licenses to participating sites that aggressively utilize=20
the product during the beta phase and provide us quality feedback. =A0If=20
you are interested, please send details regarding your testing=20
environment to info@.perfectionedge.com.
Best regards,
David Barber
Perfection Edge
http://www.perfectionedge.com
|||Nick Stansbury wrote:
> Hi,
> Are there any commercial tools available that allow easy management
> of sql server objects? Intellisense, object grouping, hiding system
> objects etc.?
> Thanks
> Nick
Try Speed IDE from Imceda over at http://www.imceda.com.
David G.

Godaddy server/SQL Management studio connection question

Hello,

I just got a personal site with godaddy that I would like to be able to connect to using SQL Server management studio

I try to connection using

Server Name: sdhfuh928323984ujf.phx3.secureserver.net

authentication: sql server

Username: my username

password: my password

Then it tries to connect and gives me an error that I can't and remote connections may not be allowed.

I followed the directions it gave and turned on remote connections, restarted the server, but I still get the error.

any ideas?

It might be a firewall issue.

Global Server Variables

Is there a way in SQL Server to setup Global Server Variables or Constants

We are working with an Off the Shelf Constituent Management application based on SQL Server. We can only read from the App's DB. So we setup another DB to run SP to access the data in the main DB. One problem we have is that there codes that the app DB uses that we need to reference as criteria in our SP. Example: Code for a phone type of email is 731. So if we want to pull email addresses we need to Select where PhoneType = 731. We found out that each time the main db is rebuilt those codes change. That means finding everytime we used that code and changing it.

It would be great to be able to set a global variable and use it anytime that code is needed.

Any ideas.The way I have accomplished this myself is to use a UDF (user-defined function). This will give you just one place to make the change.

CREATE FUNCTION [dbo].[getPhoneType]
(
)
RETURNS int AS
BEGIN
RETURN 731
END

To use it:

SELECT * FROM myTable WHERE PhoneType = dbo.getPhoneType()

Terri|||Since you've created another db to hold your add-ons, you could build tables to hold those type of values. I usually create a name/value table to hold things like key/value pairs and then just select the correct record from it when I need it.

GK - Blocking the unauthorized use of the SQL Management Console ( or third party tool

Hi to all,
Whenever SQL server is installed onto a server it can be accessed by the SQL
MC and easily registered for use. Whenever someone not authorized installs
the SQL MC or a third party tool with the same capabilities all database con
tent can be altered !! THIS
IS A VERY SERIOUS THREAD !
Is there a way to prevent this?
GKramer
The NetherlandsCan you please elaborate on your concerns? Only users who have been granted
access to SQL Server can connect and access database objects. When SQL
Server is initially installed, only members of the local Administrators
group can connect. Once you grant an account access to SQL Server, they can
use any tool but are limited by the SQL Server permissions you have granted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gkramer" <anonymous@.discussions.microsoft.com> wrote in message
news:2BDA7DD5-3FDE-45CC-BD95-BDCD8AD01BDE@.microsoft.com...
> Hi to all,
> Whenever SQL server is installed onto a server it can be accessed by the
SQL MC and easily registered for use. Whenever someone not authorized
installs the SQL MC or a third party tool with the same capabilities all
database content can be altered !! THIS IS A VERY SERIOUS THREAD !
> Is there a way to prevent this?
> GKramer
> The Netherlands
>