Credentials used to run this report are not stored.

I created a report in SSRS, and deployed it. I have created security where when users run the on demand report, it will prompt them for a username and password. I went to the properties of the report then Execution, and tried to set up report execution snapshot and got an error 'Credentials used to run this report are not stored.' I get the same error if I go to History and try to select any of the options there.

Hello,

In order to create a subscription/snapshot, you have to store credentials to run the report. If you don't store the credentials, when it goes to run unattended, it has no connection information.

Jarret

|||Where do I store these credentials?|||

Hello,

Go to the properties of your report, then click on 'Data Sources' on the left. In here, select 'Credentials stored securely in the report server' and supply a username and password. If you use a Windows account, you have to give the domain as well (domain\user) and check the box for 'Use as Windows credentials when connecting to the data source'. Hit Apply.

Hope this helps.

Jarret

|||That worked....thanks!!

Creating users programmatically

Is there a way to programmatically create users for SQL server database access?Hi,
Use the below system procedures
1. sp_addlogin (create a Login)
2. sp_adduser (Create a User)
3. sp_addrolemember (To add a db role to user/group)
4. sp_addsrvrolemember (To add a server role to Login)
THanks
Hari
MCDBA
"Me" <anonymous@.discussions.microsoft.com> wrote in message
news:E59E2633-C1CB-44C2-8859-D0D05FC2CF37@.microsoft.com...
> Is there a way to programmatically create users for SQL server database
access?|||Thanks !

Creating users programmatically

Is there a way to programmatically create users for SQL server database acce
ss?Hi,
Use the below system procedures
1. sp_addlogin (create a Login)
2. sp_adduser (Create a User)
3. sp_addrolemember (To add a db role to user/group)
4. sp_addsrvrolemember (To add a server role to Login)
THanks
Hari
MCDBA
"Me" <anonymous@.discussions.microsoft.com> wrote in message
news:E59E2633-C1CB-44C2-8859-D0D05FC2CF37@.microsoft.com...

quote:

---

> Is there a way to programmatically create users for SQL server database

---

access?|||Thanks !

creating users in sql eypress

Hi,

wanted to add a login in via c# to sql server express.

Is this possible?

I treid this:

String connString = "data source=xtrlt027;Initial catalog=master;Integrated Security=true;";
SqlConnection conn= new SqlConnection();;
conn.ConnectionString = connString;

try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
SqlCommand cmd = new SqlCommand("create login tommtk with Password='tomm2tomm'; use master; create user tommtk;", conn);
cmd.ExecuteNonQuery();
System.Console.WriteLine("Login created!!!");

}
catch (Exception deleteEx)
{
System.Console.WriteLine("SqlException Handle :{0}", deleteEx.ToString());
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}

But i get an exception, that there is an error near the keyword login and the keyword user.

So wahts wrong

can anybody help me?

Greetz
What is the exact error message ?

jens Suessmeyer.

http://www.sqlserver2005.de

|||Pretty sure each of you commands inside the cmd object need to be execute as separate statements/commands, not as one.|||Hi,

really looks like, that it is not possible to create logins in sql server express.
Downloaded sql studio management express and in an sql query it did not even recognized the key words.

The exception in my app was:

SQL syntax error near 'login'

So is it really not possible to create logins in sql express? i have the version that is included in visual Studio 2005 professional.

Greetz
|||It absolutely is possible can you please post the script you are trying to run in SSMS-E?|||

I assure you that it is possible to create Logins in SQL Express.

You need to separate certain commands for SQL to recognize them. All the CREATE commands fall into this category. You're code has them all run together as one big statement, which isn't going to work regardless of where you try to run them.

Here is some T-SQL that will accomplish what you're trying to do from the query window. You should be able to translate this to your code by simply running each statement separately. (e.g. Each thing between the GO keywords.)

USE master
GO

CREATE LOGIN tommtk
WITH PASSWORD = N'tomm2tomm'
GO

USE AdventureWorks
GO

-- For login tommtk, create a user in the database
CREATE USER tommtk
FOR LOGIN tommtk
WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'tommtk'
GO

I've actually changes the database context from master to AdventureWorks to create my user as I can't imagine why you were actually trying to create the User in master. If you really were tyring to create a user with permissions in master, just ignore the 'USE AdventureWorks' statement in your code. I've also added a call to give the User a specific database role, db_owner in this case, but that isn't required if you were not planning on assigning a specific role.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

|||Tried the following sql statement:

create login tommtk
with Password='tomm2tomm';
use master;
create user tommtk
|||Hi,

tried this statement in a developers edition:

create login tommtk with Password='tomm2tomm'; use master; create user tommtk

it worked fine, but not in the express edition. Or what edition ist it, included in VisualStudio 2005.

In the query window it does not even recognize the keyword login.

And in permission-properties i can not switch to mix of windows auth and sql server auth.
It is inactive

Greetz
|||Are you currently running windows Auth only?

creating users in sql 2000

i recently noticed that the programmer has been adding the users of our applications to the actual users under our database. When i inquired as to why, he said it was the only way they could access the data. I also noticed that the users all have been set to owners. I spoke with another friend and he said as far as he knew there wasn't any real reason why the application users would have to be set up in the actual database but just in a table/field, but that they really don't need to be owners. This app is designed to have thousands and thousands of users, and it seem like this is going to be difficult to manage.
Any thoughts?There seems to many ways you can implement security, Windows Authentication, SQL Server mode, Application Roles, individual user accounts, single application accounts.

To shorten the response I will just say how we implemented security. Like you we have about 2000 users that access the database via one of many applications. We elected to create a single user account for each application (example: Payroll - INetPayroll). Each enduser would enter there logon information into a logon screen (.ASP) and be validated against a security table that listed every user and there password. Once validated the application would then connect to the database using it's own SQL Server account (INetPayroll). This way we limited the number of user accounts that had to be maintained in the database, security would only be granted to a few user accounts, we implemented GROUPs and added users (INetPayroll) to the GROUPs, there by only needing to grant permission to the GROUP only. We couldn't use NT authentication because we deal with NOVELL users and all applications are via Intranet and connections are made with the IIS user account.

By making every user the owner you have opened up a can of worms. This is the lazy mans way of doing it. If a user has MS Access on their desktop or MS Query, they could connect to the database directly and access to database. As owners they can now modify any data directly, drop create objects, not good.

In our implementation user don't really have a SQL Server account so they can not access the database directly.

You may want to look at Application Roles.|||and as a follow on to achorozy, I NEVER allow programmers direct access to production dbs & tables. If they can't get to what they want via an application & stored procedures they are out of luck. That always has been a tuff argument to make to managment and there will always be an exception but this is one area I won't back down on. Security can never be taken to seriously.|||I don't want to side track here, but Paul brings up a good point. In our environment all data access, manipulation is done through stored procedures and not direct SQL statements. Were I work only the DBAs create the stored procedures and database objects, the developers write the VB/ASP code, design the screens, etc.

I wish I could be a little survey here and find out how many people that subscribe to this forum are in fact DBAs at there site or have a DBA at there site. Are any of you out there developers/programmers thrown into the DBA role? Do you want to take on the responsiblities of a DBA or are you just doing enough to get by, to get the system up and running?

Just some thought I have.sql

Creating user groups with Forms Authentication

Hello,
Is there a way to implement groups with forms authentication? Currently I am
only able to create users and being able to generate groups would greatly
help.
ThanksSearch the below URL for 'forms authentication groups'. I found quite a few
results:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs
--
Adrian M.
MCP
"Sceptical" <skep@.nowhere.com> wrote in message
news:HrH6e.17208$215.16616@.tornado.rdc-kc.rr.com...
> Hello,
> Is there a way to implement groups with forms authentication? Currently I
> am only able to create users and being able to generate groups would
> greatly help.
> Thanks
>sql

Creating temporary table using a template table dynamically

I'm having an application for users to extract data from sql 2K. Problem is that I need to split the huge amount of data into few pages on the application screen.

Since my order by clause could be dynamic, which is a big problem, the solution I can think of is to populate a temporary table ( within a stored proc ) and assign a column within the temporary table with a running number.

This stored proc would accept among them, an argument, which is the table name of a base table.

However, I would like to create this stored proc in such a way that I could create a temporary table based on given based table name, complete with the field sizes and types. On top of that, would like to add a new column to this temp table to store a running number key.

Opinions appreciated. :)Refer to this linkSQL Performance (http://www.sql-server-performance.com/rd_temp_tables.asp) for information.

Creating SQL Statements at run-time

Okay, guess a few questions rolled into one post here.

On my site, I have a drop down list where users can select different columns from their database, and then once they select any given field, all the unique values that the field contains are brought up in a CheckBoxList for the user to select which ones they want to search for. (Note: that part of the site is already done--this next part is what I need help with) I want to create a SQL statement based on what the user checked. So like, if from FieldX they checked Item1, Item3, and Item8, the SQL statement created should be something along the lines of:

SELECT * FROM Orders Where FieldX='Item1' OR FieldX='Item3' OR FieldX='Item8'

This is going to be in an intranet, so I'm not too worried about SQL Injection attacks, which I've heard of, but don't really know what they are particulary. Although I guess it would be better to be safe rather than sorry.

Also, as far as creating the SQL statement, some items from the database will be text and others will be numbers, so I guess I also need to know how to find out whether an item in question is a string or a number of some type so that I can know whether to enclose that item in single quotes within the SQL statement.

Okay, I think that's it for now.

Thanks in advance.

Welcome to the forums.

This question has been answered a few times in these forums. Search in these forums and if you still dont find any possible solutio post back.

Creating SQL database "Users" pulled from Active directory OU

Is there a tool, or does anyone have a script format that might automaticall
y
pull in AD accounts from an OU and put them in the Users group of a specific
database.
If there is an article or existing document on this I have not been able to
find it.
I would appreciate any help anyone can give whereas if I can't find this, I
will have to type in about 2000 users into my database from a hard copy.
There has to be a way. Even if someone can tell me what table and field the
users are listed in, I can probably script it from that.
Thanks in advance
gordonJust so I understand, when you create a database, you want SQL to
automatically pull in users AD and grant them access to the database?
"gordon" wrote:

> Is there a tool, or does anyone have a script format that might automatica
lly
> pull in AD accounts from an OU and put them in the Users group of a specif
ic
> database.
> If there is an article or existing document on this I have not been able t
o
> find it.
> I would appreciate any help anyone can give whereas if I can't find this,
I
> will have to type in about 2000 users into my database from a hard copy.
> There has to be a way. Even if someone can tell me what table and field t
he
> users are listed in, I can probably script it from that.
> Thanks in advance
> gordon|||Yes. Actually the database is already created. Right now, I have to
manually add every user in an AD OU to the "Users" group in SQL 2000. Then
I
have to assign them either Public or another role, manually. As you might
imagine, this is very time consuming. I have seen ADSI scripts pull
information from OU's in AD to tables in SQL, but not to the USERS group (yo
u
know, where you have diagrams, views, tables, users, roles etc. ) I want to
add the AD user to the USers in SQL.
Thanks for the response!!!!
"John Barr" wrote:
> Just so I understand, when you create a database, you want SQL to
> automatically pull in users AD and grant them access to the database?
> "gordon" wrote:
>|||I understand, and yes, it is possible, but it will taking some coding.You
will have to create a VBScript to read AD based on an OU name that you pass
and access SQL Server to execute a stored procedure to do dynamic SQL to add
the user if it is not existing, and grant it the rights you will need. It
will be time consuming to create, but it will eliminate the manual addtions.
You can get the VBScript to access AD in the Script Center on Microsofts
Site, and write those users into a table using ADO, then execute a Stored
Procedure. Seems a little hectic, but it would work.
"gordon" wrote:
> Yes. Actually the database is already created. Right now, I have to
> manually add every user in an AD OU to the "Users" group in SQL 2000. The
n I
> have to assign them either Public or another role, manually. As you might
> imagine, this is very time consuming. I have seen ADSI scripts pull
> information from OU's in AD to tables in SQL, but not to the USERS group (
you
> know, where you have diagrams, views, tables, users, roles etc. ) I want t
o
> add the AD user to the USers in SQL.
> Thanks for the response!!!!
>
> "John Barr" wrote:
>|||I appreciate it. I knew it would be that much work, was wondering ifanyone
had done it and I could use their code and modify it for my OU's. Or if
there were any Microsoft Technical articles showing how.
Thanks for the responses.
"John Barr" wrote:
> I understand, and yes, it is possible, but it will taking some coding.You
> will have to create a VBScript to read AD based on an OU name that you pas
s
> and access SQL Server to execute a stored procedure to do dynamic SQL to a
dd
> the user if it is not existing, and grant it the rights you will need. It
> will be time consuming to create, but it will eliminate the manual addtion
s.
> You can get the VBScript to access AD in the Script Center on Microsofts
> Site, and write those users into a table using ADO, then execute a Stored
> Procedure. Seems a little hectic, but it would work.
> "gordon" wrote:
>|||Hi
What are you not using roles and permissioning via AD group membership? It
is the recommenced way to manage users in SQL Server.
Add the users in AD to a group, then add the group the SQL Server. Then when
a new user arrives, you just add the person to the AD group and then the
user has the permission in the DB. When the user leaves, there is no
maintenance to be done at SQL Server level, only at AD level.
Books online has a lot of information DB roles and AD group membership.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"gordon" <gordon@.discussions.microsoft.com> wrote in message
news:03540F8D-A377-4207-8093-4DA0F6225D81@.microsoft.com...
> Yes. Actually the database is already created. Right now, I have to
> manually add every user in an AD OU to the "Users" group in SQL 2000.
> Then I
> have to assign them either Public or another role, manually. As you might
> imagine, this is very time consuming. I have seen ADSI scripts pull
> information from OU's in AD to tables in SQL, but not to the USERS group
> (you
> know, where you have diagrams, views, tables, users, roles etc. ) I want
> to
> add the AD user to the USers in SQL.
> Thanks for the response!!!!
>
> "John Barr" wrote:
>

Creating specific users

I have a user who is just responsible for monitoring SQL backups. However I
seem to have to grant sys admin rights in order for the user to be able to
see the backups in Enterprise manager. What is the lowest level that I can
grant to the user in this situation ?
SiSimon
Why do the users need to see the backups in EM? The files are located on
filesystem.
Can just users issue BACKUP DATABASE/RESTORE DATABASE... from QA?
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:1A221F70-5B21-4A76-97DF-4BF62A4E4196@.microsoft.com...
>I have a user who is just responsible for monitoring SQL backups. However I
> seem to have to grant sys admin rights in order for the user to be able to
> see the backups in Enterprise manager. What is the lowest level that I can
> grant to the user in this situation ?
> Si|||The whole backup regime was setup before I joined the company. They use SQL
jobs to run the SQL backups onto tape. A user has been designated to check E
M
to ensure the tape contents are correct. This will change in the medium term
as we are looking at how everything is backed up and will probably move to
something like Quest Litespeed. However in the short term I don`t really wan
t
to change the way things are done, only to change in the future.
"Uri Dimant" wrote:

> Simon
> Why do the users need to see the backups in EM? The files are located on
> filesystem.
> Can just users issue BACKUP DATABASE/RESTORE DATABASE... from QA?
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:1A221F70-5B21-4A76-97DF-4BF62A4E4196@.microsoft.com...
>
>|||Simon,
A user does not need to be sysadmin to run backups (such as through Query
Analyze as Uri mentioned), but I believe that Enterprise Manager for SQL
Server 2000 specifically checks for sysadmin in the application.
So, as long as SQL Server 2000 and Enterprise Manager are the tools, I think
you are stuck with this level of access. Perhaps your revised approach will
not be too far away.
RLF
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:26A07029-860D-40FB-B382-B064819B3A04@.microsoft.com...[vbcol=seagreen]
> The whole backup regime was setup before I joined the company. They use
> SQL
> jobs to run the SQL backups onto tape. A user has been designated to check
> EM
> to ensure the tape contents are correct. This will change in the medium
> term
> as we are looking at how everything is backed up and will probably move to
> something like Quest Litespeed. However in the short term I don`t really
> want
> to change the way things are done, only to change in the future.
> "Uri Dimant" wrote:
>|||Hi,
The minimum rights a user account needs is:
Public (can not remove)
db_backupoperator
db_denydatareader (don't need but locks down)
db_denydatawriter (don't need but locks down)
If db_denydatareader is not set the user will be able to see the tables and
the structure (table properties only), but not the data in the tables. See
the stored procedures and views there content but cannot edit them. When
selected it will hide all the tables, views, stored procedures and users
from them and they will only be able to backup the database.
The only issue you will have is if they are backing up to disk they will not
be able to browse the DIR structure but can enter the location manually.
Alternatively you can set it up as a backup device.
- Mike
----
--
<a href="http://links.10026.com/?link=http://www.cogitar.net"> Cogitar Software. (
http://www.cogitar.net ) </a>
http://www.cogitar.net Cogitar Software. (Software Development and Managment
Systems)
http://www.web-dominion.co.uk Web-Dominion. (Web Design and hosting )
http://shop-dominion.com (senery landscape pictur gallery)
----
--
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:1A221F70-5B21-4A76-97DF-4BF62A4E4196@.microsoft.com...
>I have a user who is just responsible for monitoring SQL backups. However I
> seem to have to grant sys admin rights in order for the user to be able to
> see the backups in Enterprise manager. What is the lowest level that I can
> grant to the user in this situation ?
> Si

Creating security roles

Hello,

I have users with different roles accessing my system.

For eg, for database Adventure:

Role 1 - Can only read write data but not update

Role 2 - Can read, write and update

Role 3 - Can search data and run reports

Role 4 - Can backup, create users, replication, Run SSIS.

How do I create scripts for such users.

Regards,

Vidya.

Do it the easy way. Create the roles in the GUI and then hit the "Script" button at the top.

And you may want to grant permissions to the schemas, so that new tables you add will have the right permissions.

Rob|||

Look up Server, Integration Services andDatabase Level roles in books online.

This should get you started:

Role 1 - Can only read write data but not update db_datareader

Role 2 - Can read, write and update db_datawriter and db_datareader

Role 3 - Can search data and run reports db_datareader, run reports is outside of T-SQL jurisdiction. Try the Reporting Services forum

Role 4 - Can backup - BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

create users, requires ALTER ANY USER rights, or db_owner

replication, Not sure, might be CONTROL SERVER server privilege or sysadmin,

Run SSIS db_dtsadmin, db_dtsltduser, and db_dtsoperator

|||thanks

Creating new users with MSDE

I have MSDE installed on my sistem, and I need to add a new user.
How do I go about doing this?Hi,

MSDE do not have user interface thus all manipulation can be done
using SQL-DMO and Visual Basic (Assume you are using VB6.0).

Add reference to "Microsoft SQL-DMO object librarary", in that you can
use login class to add login. In fact you can completely manage and
operate SQL Server/MSDE using SQL-DMO.

Hope this helps you.

Thanks, Amit

thirdmarshaleomer@.hotmail.com (Kyle) wrote in message news:<2d9cd550.0306271223.27e14f57@.posting.google.com>...
> I have MSDE installed on my sistem, and I need to add a new user.
> How do I go about doing this?|||if you have 19$ take a look at www.msde.biz

hth,
Helmut

"Kyle" <thirdmarshaleomer@.hotmail.com> schrieb im Newsbeitrag
news:2d9cd550.0306271223.27e14f57@.posting.google.c om...
> I have MSDE installed on my sistem, and I need to add a new user.
> How do I go about doing this?

creating new users with dbo permissions

I am trying to create a new user for a SQL Server database and use the credentials in an
ASP.NET app.

Problem is dbo permissions are not being applied to the database objects when I set up a
new user (Logins -> New Logins) with SQL Server Authentication, set the default database to
the database I want.

I then get an error message saying that the user has not been granted access to their default
database : DBNAME and therefore will not be able to gain access to their default database.

I'm not sure why this is as I am logged in as sa.

Anyway, when I ignore the error and set-up this new user through the Users section of the
database none of the dbo permissions carry over.

Can anyone help?
Cheers.SQL Server's default authentication is via Windows only and you have to invoke
authentication via SQL Server usernames/passwords by right clicking on the server name, selecting the Security tab and making the change.

Creating new tables for new users

hi all,

How can i create a new data table automatically for new users signing into the website......what i mean here is that i have a predefined database table and i want that users signing in can have that table for them unique , so that they can fill data for themselves and that data will be visible to all just like forums

Hello Pankaj,

You can instantiate a new DataTable in the session where the user can modify his data.

But another possibility is to add a column like userid or username to the predefined table and write business logic that allows the user to modify its own records (where his username matches the username in the records) and other users can only read the records.

In this case a user is owner of his records (has it unique to himself for updates).

Jeroen Molenaar.

Creating Error Output for Custom Components

Hi,

I have a 2 custom components - source and destination.

I want to create an error output for each, to allow the users of my component to handle errors the way they choose.

I only found a property in IDTSOuptut90 named isErrorOut - a boolean property indicating whether this output is an error output or not.

Does anyone have additional documentation / articles / code samples regarding how to really populate the rows in the error output?

Thanks

You add the output in ProvideComponentProperties, as others, and that property is pretty much the key-

// Error Output
IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = ErrorOutput;
outError.Description = "Error output for rows that caused an unexpected error.";
outError.SynchronousInputID = inp.ID;
outError.ExclusionGroup = 1;
outError.IsErrorOut = true;

In ProcessInput you would then direct rows to thje normal output or the error output.

int iError = ComponentMetaData.OutputCollection[ErrorOutput].ID;
buffer.DirectErrorRow(iError, 0, buffer.CurrentRow);

I don’t believe there are no samples in the recent batch on Microsoft.com/downloads/ and from memory I’m sure the Source sample that ships with the product includes error output usage.