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

Gr

I am creating a report in visual studio.
I pass in a field called delivery date
i want the report in 3 sections
Overdue, Today and future
however i am as yet able to find a way to have these 3 logical groupings
thanks frankI need it to know what todays date is.

i did have it working howver for each date it put in the heading|||>>I need it to know what todays date is.

Use currentdate in the formaulasql

Got Started But Not Really: SQL Server does not allow remote connections

I downloaded VS studio express, and created a personal website using the wizard to test drive the thing. When I hit ctr + f5 to run the sample provided, then I get this message:

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) "

I tried disabling the firewall (Norton's firewall because Windows is disable always). Any suggestions? This is a brand-new machine, running XP sp2, just downloaded the VS studio express.

I really appreciate your inputs.

Regards,

Carlos

info@.paesano.com

Have a look at the following KB Article.

How to configure SQL Server 2005 to allow remote conections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

|||

Hi Glenn,

Thanks a lot for the answer. I followed the instructions provided in the article. Everything fine until I tried to start the SQL Express, which at the end gave me an error message: An error ocurred while performing this operation.

It seems like something is preventing SQL to start. I disabled my firewall thinking that the firewall was the cause of it, but it did not work either. If you have any suggestions, please help. As I mentioned, this is a brand-new machine running on Win SP2.

Thanks in advance.

Carlos

|||

As this looks more like a sql server express problem I will move the thread to the sql express group/

But in the mean time I would have a look at the event log for the machine to see if there are any errors being returned. With out any more details on the actuall error it is going to be hard to help.

Going from Express to SQL 2005 Standard

I have used VWD and SQL Express with success. Now I have uninstalled SQL Express and installed SQL Server 2005 Standard along with Visual Studio 2005 Standard editions. The problem that I am having is when I go to use the ASP .Net configuration tool, it cannot connect to the new instance.

Now I did run the aspnet_regsql utility to create the membership tables.

Did I do something wrong in the setup of the new SQL Server 2005? I have been trying to get an answer and find an answer to this problem for 2 days now.

Thanks for any help.

Have you posted this on the ASP.Net forums? (http://forums.asp.net/146/ShowForum.aspx)

What is the specific error message you are seeing?

|||I have posted it to ASP .Net forums and have not got an answer. I did find out the answer this weekend though. It was something in the web.config file that was note properly syntaxed.

Thanks.

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.